How to Use DATEDIF Function in Excel – 6 Examples

The DATEDIF function is made to make it easier for us to figure out how much time has passed between two dates. The number of days, months, or years among two dates can be easily determined using the DATEDIF function. The Excel Date/Time functions family include the DATEDIF function.

Syntax of DATEDIF Function

=DATEDIF(date1, date2, unit)

Argument of DATEDIF Function

date1- the beginning of the time period you would like to calculate.

date2– end date of the time span you want to calculate

unit– defines the unit of time or how you want to figure out how many dates has passed between two dates

Please see the table below:

Notes: 

• date1 and date2 can be dates or the equivalent number of dates according to the 1900 system of Excel.
• date1 should be any preceding date of date2. Otherwise, the  #NUM! error will display in the output cell.

Return of DATEDIF Function in Excel

Based on the unit provided in the argument section, the DATEDIF function returns the difference between the start and end dates of a particular time span.

Example #1: Using the DATEDIF Function, Count Time Difference in Terms of Date, Month, or Year

In this example, we will count the difference between the dates given in the dataset below.

Apply the following formula to find the difference according to the units provided 

=DATEDIF(B3,C3,"D")        //returns date difference between two dates

=DATEDIF(B3,C3,"M")       //returns month difference between two dates

=DATEDIF(B3,C3,"Y")      //returns year difference between two dates

Explanation:

Focus solely on row 6, the #NUM! error was returned because the end date was earlier than the start date.

Example #2: Application of the DATEDIF Function to Count Time Difference Excluding Date, Months, or Year.

With reference to the previous example, apply the following formula to get the time difference excluding the date, month, or year.

=DATEDIF(B3,C3,"MD")     //returns date difference excluding month and year

=DATEDIF(B3,C3,"YD")      //returns date difference excluding year 

=DATEDIF(B3,C3,"YM")     //returns date difference excluding year and days

Example #3: Ignoring #NUM! Error Using the DATEDIF Function

You may notice that the #NUM! error returned in the examples above. In this example, let’s find a solution to this problem.

Use the IF function to check whether the “Start Date” is preceding or succeeding the “End date”.

=IF(B3>C3,DATEDIF(C3,B3,"D"), DATEDIF(B3,C3,"D"))

Explanation:

• According to the argument of the DATEDIF function, the Start Date should be earlier than the End Date.
• The IF function checks the condition B3>C3. Where B3>C3 means the ‘’Start date’’ later than the ‘’End Date’’.
• If B3>C3 satisfies, then the IF function triggers the DATEDIF(C3,B3,”D”) function to count the date difference. 
• In the formula DATEDIF(C3,B3,”D”) – the value of cell C3 is provided as the start date and B3 as the end date. As the Start Date and the End Date swapped, the #NUM! Error eliminated.
• If B3>C3 is not satisfied, then the IF function triggers the function DATEDIF(B3,C3,”D”) to count the date difference.

Example #4: Counting Weeks of Time Span Using the DATEDIF Function

If we divide the counted date by seven, then we will have the week difference.

But, the result can have some fractional value. So, we will use the ROUND function, which will round the result to the nearest value of zero.

=ROUND(DATEDIF(B3,C3,"D")/7,0)

Example #5: Getting the time Difference of Date, Month, and Year in a Single Formula.

Now, we want to calculate the time difference in terms of date, month, and year using a single formula, and the output will be displayed in a single cell.

=DATEDIF(B4, C4, "md") &" days, "&DATEDIF(B4, C4, "ym") &" months, "&DATEDIF(B4, C4, "y") &" years"

Now, We want to eliminate the texts like 0 years, 0 months, or 0 days

The following formula will return just non-zero elements

=IF(DATEDIF(B3,C3,"md")=0,"",DATEDIF(B3,C3,"md")&" days ")&IF(DATEDIF(B3,C3,"ym")=0,"",DATEDIF(B3,C3,"ym")&" months ")&IF(DATEDIF(B3,C3,"y")=0,"",DATEDIF(B3,C3,"y")&" years ")

Example #6: Make an Age Calculator Using the DATEDIF Function

In general, to calculate age, we have to find the difference between the present date to the birth date.

To pick the present date, we will use the YEAR function as below:

=DATEDIF(B3,TODAY(),"Y")        //returns the year difference from the birth date to the present date.

where B3 is the cell containing the birth date.

Now, we want to create a formula to return the age in terms of date, month, and year.

=DATEDIF(B3,TODAY(),"y") & " years, " & DATEDIF(B3,TODAY(),"ym") & " Months, " & DATEDIF(B3,TODAY(),"md") & " Days"

Things to Keep in Mind about DATEIF Function:

Excel emits the #Value! error when it fails to identify the date provided due to the failure to recognize the data argument.

#NUM! error – This emerges when the start date in the argument exceeds the end date.

Difference Between DATEDIF Function and MONTH, YEAR Function

If you want to calculate the month difference between two dates, you can simply apply the DATEDIF function as below.

=DATEDIF(B3,C3,"M")

The same result can be obtained by using the YEAR and MONTH functions as below:

=(YEAR(C3) - YEAR(B3))*12 + MONTH(C3) - MONTH(B3)

The difference result is provided for rows 6 and 7. 

The YEAR and MONTH functions don’t follow any preceding rule. Unlike the DATEDIF function, the YEAR and MONTH functions just return negative or positive values.

The DATEDIF function counts a complete chronological calendar. In row 7, the date difference is 178. As the complete six months are not covered yet, five months are returned by the DATEDIF function. 

But the YEAR and MONTH functions don’t care about a complete chronological calendar. as the dates stay six months apart. So, the return is six.