Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

DATEDIF

Determine the difference between two dates in Excel
Find the difference between two dates.

Rational

Determine the difference between two dates.

Description

The Excel DATEDIF is an unknown compatibility function that can be used to calculate the difference between two dates. Example, to find a number of years between two dates, you may use =DATEDIF(start_date, end_date,”y”).

This function does not show in the Insert Function dialog box or Formula AutoComplete list. Therefore, enter this formula manually.

Syntax

=DATEDIF(start_date,end_date,unit)

Parameters

start_date – The start date
end_date – The day until the difference is to calculate
unit – The time unit option differ as per the required result. The DATEDIF include six different option for unit argument.

Return Value

Integer representing the difference

Key Notes

Calculate the absolute difference between two dates based on the selected option in the unit parameter. The output will be in term of a number of days, months, or years as a difference between two dates. To find difference of days we can use separate function as well.

Note: The start_date argument must be before the end_date argument. Otherwise, the function returns an error.

The DATEDIF (Date Difference) function originates from Lotus 1-2-3, and for compatibility purpose, Excel includes as part of function although as a secret one.

The DATEDIF is a handy function to calculate the difference between start_date and end_date, but the result of this function depends on one of the six options for the third argument.

The following table provides valid codes for the third argument:

Unit                    Meaning                                                     Result
“d”DaysThe number of days between start_date and end_date
“m”MonthsThe number of complete months between start_date and end_date
“y”YearsThe number of complete Years between start_date and end_date
“ym”Months excluding days and yearsReturn number of months between start_date and end_date,
ignoring days and years between two dates.
“yd”Days excluding yearsReturn number of days between start_date and end_date,
ignoring years between two dates.
“md”Days excluding months and yearsReturn number of days between start_date and end_date,
ignoring the months and years between two dates.

Example:

Let’s check some examples to understand the application of Excel DATEDIF Function in the light of third arguments. Third argument based on Unit info. (above table) returns different type of value:

  • To find difference of years between two dates we can simply DATEDIF function =DATEDIF(C6,D6,”y”), where cell reference is year.
    Similarly we can calculate Months and Days between two dates.   
  • Using DATEDIF Excel function we can calculate “Days difference as if they were in the same month & same year”, using ‘md‘ as unit parameter.
  • We can use DAYS function as well to returns difference between two dates.

Keep Reading Similar Functions:

Find a number of working days between a date range in Excel

NETWORKDAYS

The NETWORKDAYS function returns a number of working days between two dates, 01-11-2018 (B6) and 31-12-2018 (C6), use =NETWORKDAYS(B6,C6) that returns ‘43’.

Read More »
Find a number of working days with custom weekend days in Excel

NETWORKDAYS.INTL

The NETWORKDAYS.INTL counts days between two dates, excl. custom Weekend days. I.e.; workdays between 01-11-2018 (B6) and 31-12-2018 (C6) excl. Friday and Saturday, use =NETWORKDAYS.INTL(B6,C6,7) that returns 43.

Read More »
Find the difference between two dates in Excel

DAYS

The DAYS function returns the difference between two dates. To find days duration between two dates, use DAYS(“01/08/2018″,”01/05/2017”) that returns ‘457’.

Read More »
Find the difference between two dates, based on a 360 days year cycle in Excel.

DAYS360

The Excel DAYS360 function result assumes that a Year is of 360 days, 30 days per month.
use =DAYS360(“01/05/2017”, “01/08/2018”) that returns ‘450’.

Read More »
Return a month before or after the start date in Excel

EDATE

The EDATE function returns a future or past month from a given date. I.e., =EDATE(“31-03-2016”,-1) returns a leap date 29-02-2016 as 2016 is a leap year.

Read More »