How to return Multiple Items using DATEDIF

The DATEDIF function is a must-learn function if you want to do date calculations.

Generally, it is used to get the difference between two dates.

Here is what you need to know to use the function properly:

1. It is not a document, so it will not populate among Excel Date functions when you type it.
2. Since it is not documented, Excel does not provide native IntelliSense functionality. This means you will not enjoy the pop-up description regarding the function and its parameters.
3. You have to remember the below syntax.

Syntax:= DATEDIF(start_date, end_date, Units)

Note:

*end_date should always be greater (recent) than the start_date else; the function returns an error.

*You can use single or multiple units below

Here is the difference between the double-digit units. Assume the start date is 1st March 2013 and end date is 7th July 2023

1. Since the days in the Month (MD) ignores the month and year part of the date, it subtracts the day of the end date from the start month.
2. Since the days in the year (YD) ignores the year part of the date, it calculates the difference between the end date month (July 7th) and the start date month (March 3rd) in terms of days.
3. Since the months in the year ignores the year part of the dates, it calculates the difference between the month part of the end date (July) and the month part of the start date (March)

DATEDIF single Unit calculation

In the example below, we want to return the age of the employees in terms of complete years.

Note: Today’s Date is 7/7/2023

DATEDIF Multiple Unit calculations

Given a staff hire date, how do you return his complete years, months & days of service?

Easy! Use an array of units in the DATEDIF Function

DOWNLOAD WORKBOOK