OzGrid

=EDATE

Add a specified number of months to a date in Excel

=EDATE(start date, number of months)

=EOMONTH

Convert a date to the last day of the month (e.g., 8/19/2019 to 8/31/2019)

=EOMONTH(A2,1)

Date of the last day of the month, one month after the date in A2

=EOMONTH(A2,-3)

Date of the last day of the month, three months before the date in A2.

=DATE

Returns a number that represents the date (yyyy/mm/dd) in Excel

=DATE(2016, 6, 20) - returns a serial number corresponding to 20-June-2016

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - returns the first day of the current year and month

=DATE(2016, 6, 20)-5 - subtracts 6 days from May 20, 2016

=TODAY

Insert and display today’s date in a cell

=TODAY() - returns todays date

=TODAY()+7 – adds 7 days to the current date

=WORKDAY(TODAY(), 30) – adds 30 weekdays to todays date excluding weekends

=DATEVALUE(date_text) 

Converts a date in the text format to a serial number that Microsoft Excel recognizes as a date

=DATEVALUE("20-May-2015")

Returns 42144

=TEXT(value, format_text)

TEXT function in Excel to convert date to text

=TEXT(value, format_text)

For example, you can use the following formula to

=TEXT(B1,"mm/dd/yyyy")

Converts a date in cell B1 to a text string in the traditional US date format (month/day/year)

For example June, 8, 2016 to 06/08/2016

Note, this will be aligned left because it is text format and not date format

=DAY

=DAY(TODAY())

Provides the day of today's date

=WEEKDAY

=WEEKDAY(TODAY()) 

Provides a number corresponding to today's day of the week

=DATEIF

DATEDIF(start_date, end_date, unit) calculates the difference between 0 dates in days, months or years.

=DATEDIF(b2, TODAY(), "d") calculates the number of days between the date in b2 and today's date.

=DATEDIF(B2, B5, "m") providesthe number of complete months between the dates in B2 and B5.

=DATEDIF(B2, B5, "y") provides the number of complete years between the dates in B2 and B5.

= EDATE

=EDATE(B2, 5)  adds 5 months to the date in cell B2.

=EDATE(TODAY(), -10)  subtracts 10 months from today's date.

=WORKDAYS

WORKDAY(start_date, days, [holidays]) function provides a date ‘N’ workdays before or after the start date. It automatically excludes weekend days from calculations.

=WORKDAY(A1, 65, B2:B85)

adds 65 weekdays to the start date in cell A1, ignoring holidays in cells B2:B8

=NETWORKDAYS

Provides the number of whole workdays between two specified dates.

NETWORKDAYS(start_date, end_date, [holidays]) function provides the number of weekdays between two dates that you specify. It automatically excludes weekend days.

=NETWORKDAYS(C2, D2, E2:E5)

calculates the number of whole workdays between the start date in C2 and end date in D2, ignoring Saturdays and Sundays and excluding holidays in cells E2:E5

=MONTH

=MONTH(A2) provides the month of a date in cell A2.

=YEAR

Extracts and displays the year from a date (e.g., 7/18/2018 to 2018) in Excel

=YEAR(TODAY()) 

Provides the current year

Say A2 has 20 June 2016 then

=YEAR(A2) - Provides the year of a date in cell A2.

=YEARFRAC

Expresses the fraction of a year between two dates

=YEARFRAC(startdate,enddate) (e.g., 1/1/2018 – 6/6/2018 = 0.43)

=YEARFRAC(A1,B1)

How to convert time to seconds

If the time in Cell B2 is 2:40:35 AM and the total number of seconds is required then:

1 Make sure the time is formatted properly – go to Format Cells (press Ctrl + 1) and make sure the number is set to Time.

2 Use the =HOUR(B2) formula to get the number of hours from the time and multiply by 3600 (there are 3600 seconds in every hour)

3 Use the =MINUTE(B2) formula to get the number of minutes from the time and multiply by 60 (60 seconds in every minute)

4 Use the =SECOND(B2) formula to get the number of seconds from the time

5 Add up the three pieces to get the total seconds

6 Make sure it’s formatted properly as a Decimal, and not as Time (press Ctrl +1 and select Decimal)

=NOW()

=NOW() provides the current date and time as of today

Date Codes:

Month

·         m - month number without a leading zero.

·         mm - month number with a leading zero.

·         mmm - short form of the month name.

·         mmmm - long form of the month name.

Date Codes:

Days

d - days number without a leading zero.

dd - day number with a leading zero.

ddd - abbreviated day of the week.

dddd - full name of the day of the week.

Date Codes:

Years

yy - two-digit year.

Yyyy - four-digit year.

Convert US date format of mm/dd/yyyy to UK (rest of the world) format of dd/mm/yyyy

=TEXT(A1,"mm/dd/yyyy")

Date Codes:

Hours

·         h - hours without a leading zero, as 0-23.

·         hh - hours with a leading zero, as 00-23.

Date Codes:

Minutes

·         m - minutes without a leading zero, as 0-59

·         mm - minutes with a leading zero, as 00-59

Date Codes:

Seconds

·         s - seconds without a leading zero.

·         ss - seconds with a leading zero.

Date Codes:

Periods of the day

·         AM/PM - displays as AM or PM.

·         If not specified, 24-hour time format is used.

Free Training Course: Lesson 1 - Excel Fundamentals