5 Awesome Date Functions in Excel

In this blog post, we explore five of the best date functions in Excel. There are many awesome date functions, so it is difficult to choose only five.

This includes date functions to calculate the number of working days between two dates, and automatically finding the date in exactly two months time.

Let’s dive into these awesome date functions in Excel..

TODAY

The TODAY function is used for a lot of good date calculation work. It returns the current date using your system clock.

This is essential for so much of what you may be trying to do in Excel. As the date changes every day, this function is used for getting your Excel features or formulas to roll with time, so that they work every day.

It is written as below;

=TODAY()

No information is needed by this function as it gets what it needs from your computer.

The video below shows the TODAY function being used to highlight dates older than 30 days.

NETWORKDAYS

The NETWORKDAYS function is used to calculate the number of working days between two dates. It is written as;

=NETWORKDAYS(start_date, end_date, [holidays])

The function will exclude Saturday and Sundays, as they will be considered weekend dates. There is a NETWORKDAYS.INTL function that is more flexible. This function allows you to specify which days of the week are worked.

Holidays is optional, and does not need to be supplied to the function. Holidays should be entered as a range of cells on your spreadsheet that contain additional non-working days to exclude from the result.

For example, the NETWORKDAYS function can be used to calculate the number of working days between the date an order was taken and when it was dispatched.

The following formula, returns the working days difference. Additional non-working days are referenced in range E2:E4.

=NETWORKDAYS(A2,B2,$E$2:$E$4)

EDATE

The EDATE function returns the date a specified number of months before or after a start date. Its syntax is;

=EDATE(start_date, months) 

For example, the EDATE function can be used to calculate the end date of a contract. The formula below returns the date exactly 3 months from the dates in column C.

=EDATE(C2, 3)

WORKDAY

The WORKDAY function returns a date a specified number of workdays before or after a start date. It is written as;

=WORKDAY(start_date, days, [holidays])

This function can be used to calculate the expected finish date of a task or project, when given a start date and a number of working days duration.

The following formula, returns the date a specified number of working days (cell C2) from a start date. It excludes the optional Holidays argument.

=WORKDAY(B2,C2)

One of the best date functions in Excel. It can also be used to return a date a specified number of workdays into the future, when using Conditional Formatting to alert you to upcoming due dates.

The WORKDAY function was also used in the Excel Gantt chart template of this blog.

There is also a WORKDAY.INTL function in Excel which is a much more flexible alternative to WORKDAY.

DATEDIF

The DATEDIF function is used to compare two dates in Excel and return the difference in years, months or days.

This function is not documented in Excel due to known limitations with the “md” unit, so when entering it into a cell you will not get any information. However its syntax is;

=DATEDIF(start_date, end_date, unit)

The unit should be entered as a string so using double inverted commas. Use the first letter of the interval you wish to return e.g. “y” for years or “m” for months.

The interval can also be entered as a combination. So for example “ym” would calculate the number of months between the two dates excluding years. This returns a result as if the dates were in the same year.

The DATEDIF function could be used to calculate a person’s age. For example, the formula below will calculate the age of a person as of the current date, where cell B2 contains the person’s date of birth.

=DATEDIF(B2,TODAY(),"y")

If you wanted to return the persons age as how many years and months old they are, we could use the formula below. The image shows the formula split over two lines for easier reading.

=DATEDIF(B2,TODAY(),"y")&" years "&DATEDIF(B2,TODAY(),"ym")&" months"

This formula uses the ampersand to concatenate two DATEDIF functions and some text.