How to Use DATEDIF Function in Excel (2 Examples)

Overview of Excel DATEDIF Function

Today I will be showing how you can work with the DATEDIF function of Excel.


DATEDIF Function of Excel (Quick View)

Overview of Excel DATEDIF Function


Download Practice Workbook


EXCEL DATEDIF Function: Syntax & Arguments


Summary

  • The DATEDIF function returns the difference of days, months or years between two dates, based on three arguments, one starting end, one ending date and one argument called unit.
  • The unit argument tells Excel whether the difference of days, months or years is to be provided.
  • The DATEDIF function includes only the ending date while counting, not the starting date.
  • DATEDIF is a hidden function of Excel. That means you can use it, but Excel will not help you by providing any syntax or short notes while you are typing it.

Syntax

=DATEDIF(start_date,end_date,unit)

Arguments

Argument Required or Optional Value
start_date Required The date from where you want to count the difference. It can be a single date or an array of dates.
end_date Required The date where you want to stop counting. It also can be a single date or an array of dates.
unit Required A text value that tells Excel whether the difference of days, months or years is to be returned.

Note: 

  • DATEDIF counts a total of six different types of differences. It depends on the argument unit.
Unit Difference Type
“d” Days between two dates.
“m” Months between two dates.
“y” Years between two dates.
“md” Days between two dates ignoring months and years.
“ym” Months between two dates ignoring years.
“yd” Days between two dates ignoring years.
  • It includes only the ending date, not the starting date while counting.
  • The DATEDIF function has the Array Formula for both the start_date and the end_date arguments. That means, it can accept an array of dates in place of a single date in the start_date argument and can return the output for each of the start_dates together. Same for the end_date argument.

Return Value

It returns a total of six different types of outputs.

  • Days between two dates
  • Months between two dates
  • Years between two dates
  • Days between two dates ignoring the months and years.
  • Months between two dates ignoring the years.
  • Days between two dates ignoring the years.

Explanation

  • When the unit argument is “d”, the function returns the plane difference of days between the starting date and the ending date.

For example, the difference of days between 4-Mar-2020 and 14-Jul-2022 is 862 days.

Excel DATEDIF with Unit d

  • When the unit argument is “m”, the function returns the plane difference of months between the starting date and the ending date.

For example, the difference of months between 4-Mar-2020 and 14-Jul-2022 is 28 months (excluding the starting month of March).

Excel DATEDIF with Unit m

But when the day of the end_date is less than the day of the start_date, it doesn’t count the last month.

For example, if the start_date is 23-Mar-2020 and the end_date is 16-Aug-2020, it will count 4 months, not 5.

Excel DATEDIF with Unit m Again

  • When the unit argument is “y”, the function returns the plane difference of years between the starting date and the ending date.

For example, the difference of years between 4-Mar-2020 and 14-Jul-2022 is 2 years (excluding the starting year 2020).

Excel DATEDIF with Unit y

But when the month of the end_date is less than the month of the start_date, it doesn’t count the last year.

For example, if the start_date is 23-Mar-2020 and the end_date is 16-Feb-2022, it will count just1 year, not 2.

Excel DATEDIF with Unit y Again

  • When the unit argument is “md”, the function returns the difference of days between the starting date and the ending date, ignoring the months and years

For example, the difference of days between 4-Mar-2020 and 14-Jul-2022 is 10 days ignoring the months and years.

Excel DATEDIF with Unit md

But when the day of the end_date is less than the day of the start_date, it counts days from one month before the end_day month.

For example, if the start_date is 23-Mar-2020 and the end_date is 16-Aug-2020, it will count days from 23-July to 16-Aug, 24 days.

Excel DATEDIF with Unit md Again

  • When the unit argument is “ym”, the function returns the difference of months between the starting date and the ending date, ignoring the years

For example, the difference of months between 4-Mar-2020 and 14-Jul-2022 is 4 months ignoring the years.

Excel DATEDIF with Unit ym

But when the month of the end_date is less than the month of the start_date, it counts months from one year before the end_date.

For example, if the start_date is 16-Aug-2020 and the end_date is 12-Mar-2022, it will count months from Aug 2021 to March 2022, a total of 6 months.

Excel DATEDIF with Unit ym Again

  • When the unit argument is “yd”, the function returns the difference of days between the starting date and the ending date, ignoring the years

For example, the difference of days between 4-Mar-2020 and 14-Jul-2022 is 132 days ignoring the years.

Excel DATEDIF with Unit yd

But when the day of the end_date is less than the day of the start_date, it counts days from one year before the end_day.

For example, if the start_date is 16-Aug-2020 and the end_date is 12-Mar-2022, it will count days from 16-Aug-2021 to 12-Mar-2022, total 208 days.

Excel DATEDIF with Unit yd Again


Formulas Utilizing Excel’s DATEDIF Function


1. Counting the Service Years of Office Employees

We can count the service years of the employees of an office using the DATEDIF function of Excel.

Look at the dataset below.

We have the names of five employees and their joining dates of a company named Johnson group.

A Data Set in Excel

Now we want to calculate the service years of each of the employees.

We can accomplish this using the DATEDIF function of Excel along with the TODAY function.

Enter this formula in the first cell.

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

And then drag the Fill Handle through the rest of the cells.

DATEDIF Formula in Excel

See, we got the service years of each of the employees.

Explanation of the Formula

  • TODAY() function returns today’s date. To know more about this function visit this link.
  • DATEDIF(C4,TODAY(),”y”) returns the difference of the years between the date in cell C4 (3/1/2005) and today. This is the required service year of Morris Bohr.
  • When the Fill Handle is dragged, the formula is copied to the rest of the cells with the increase of cell references of the start_date arguments.

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

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

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

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

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

These return the service years of the rest of the employees.

  • But the DATEDIF function can also take an array of dates as the start_date argument. So instead of using one date at a time and then dragging the Fill Handle, we can use an Array Formula to enter all the starting dates together and can receive all the service years together too.
=DATEDIF(C4:C8,TODAY(),"y")

DATEDIF Array Formula  

The Array Formula actually does the same thing as the Fill Handle. It consists of a total of 5 formulas.

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

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

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

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

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

Thus returns the service years of each of the employees.

[Do not forget to press Ctrl + Shift +Enter unless you are in Office 365, to use the array formula.]
Formula Output  Explanation
=DATEDIF(C4,TODAY(),”y”) 16 Returns the difference of years between the date in cell C4 and today.
=DATEDIF(C5,TODAY(),”y”) 14 Returns the difference of years between the date in cell C5 and today.
=DATEDIF(C6,TODAY(),”y”) 13 Returns the difference of years between the date in cell C6 and today.
=DATEDIF(C7,TODAY(),”y”) 9 Returns the difference of years between the date in cell C7 and today.
=DATEDIF(C8,TODAY(),”y”) 18 Returns the difference of years between the date in cell C8 and today.

Read more: How to Use DATE Function in Excel


2. Counting the Age from a Given Birthday

Now we will calculate the age from a given birthday.

Look at the new data set. We have the names of 5 students and their birthdays of a school named Sunflower Kindergarten.

Another Data Set in Excel

Now we will try to find out the age of each of the students up to today.

Calculating Years

To find out the years insert this formula in the first cell:

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

And then drag the Fill Handle.

Or insert this Array Formula in the first cell and press Ctrl + Shift + Enter.

=DATEDIF(C5:C9,TODAY(),"y")

In either case, you will get the years of age of each student up to today.

Age Year Formula with DATEDIF

Explanation of the Formula

  • DATEDIF(C5,TODAY(),”y”) returns the difference of years between the date in cell C5 and today. These are the years of the ages of the students.
  • By dragging the Fill Handle or using the Array Formula, an array of formulas are created like this:

DATEDIF(C6,TODAY(),"y")

DATEDIF(C6,TODAY(),"y")

DATEDIF(C7,TODAY(),"y")

DATEDIF(C8,TODAY(),"y")

DATEDIF(C9,TODAY(),"y")

They all return the corresponding years of the students.

Note: In the case of the fourth and fifth students, the years should be 2021-2012=9 and 2021-2010=11 respectively. (2021 is the present year)

But we have got those as 8 and 10.

Can you tell why?

This is because the months of the starting days ( 7 and 12) are less than the months of the ending days (August, 8).

If the month of the ending day is less than the month of the starting day, DATEDIF does not count the last year.

Formula Output Explanation
=DATEDIF(C5,TODAY(),”y”) 7 Returns the difference of years between the date in cell C5 and today.
=DATEDIF(C6,TODAY(),”y”) 9 Returns the difference of years between the date in cell C6 and today.
=DATEDIF(C7,TODAY(),”y”) 6 Returns the difference of years between the date in cell C7 and today.
=DATEDIF(C8,TODAY(),”y”) 8 Returns the difference of years between the date in cell C8 and today.
=DATEDIF(C9,TODAY(),”y”) 10 Returns the difference of years between the date in cell C9 and today.

Read more: How to Use DAY Function in Excel


Calculating Months

Now to count the months of the ages, either insert this formula in the first cell and then drag the Fill Handle:

=DATEDIF(C5,TODAY(),"ym")

Or enter this Array Formula in the first cell and then press Ctrl + Shift + Enter:

=DATEDIF(C5:C9,TODAY(),"ym")

Age Month Formula with DATEDIF

Explanation of the Formula

  • DATEDIF(C5,TODAY(),”ym”) returns the difference of months between the date in cell C5 and today, ignoring the years. This is the required month of the age.
  • By dragging the Fill Handle or using the Array Formula, the formula is repeated for the rest of the cells, like in the previous case.
Formula Output Explanation
=DATEDIF(C5,TODAY(),”ym”) 5 Returns the difference of months between the date in C5 and today, ignoring the years.
=DATEDIF(C6,TODAY(),”ym”) 7 Returns the difference of months between the date in C6 and today, ignoring the years.
=DATEDIF(C7,TODAY(),”ym”) 3 Returns the difference of months between the date in C7 and today, ignoring the years.
=DATEDIF(C8,TODAY(),”ym”) 8 Returns the difference of months between the date in C8 and today, ignoring the years.
=DATEDIF(C9,TODAY(),”ym”) 7 Returns the difference of months between the date in C9 and today, ignoring the years.

Calculating Days

To calculate the days of the ages of the students, either enter this formula in the first cell:

=DATEDIF(C5,TODAY(),"md")

And then drag the Fill Handle.

Or enter this Array Formula in the first cell and press Ctrl + Shift + Enter:

=DATEDIF(C5:C9,TODAY(),"md")

Age Day Formula with DATEDIF

In either case, we get the days of the ages of the students.

Explanation of the Formula:

  • DATEDIF(C5,TODAY(),”md”) returns the difference of days between the date in cell C5 and today, ignoring the months and years. This is the required day of the age.
  • By dragging the Fill Handle or using Array Formula, the same formula is copied to the rest of the cells.
Formula Output Explanation
=DATEDIF(C5,TODAY(),”md”) 2 Returns the difference of days between the date in C5 and today, ignoring the months and years.
=DATEDIF(C6,TODAY(),”md”) 2 Returns the difference of days between the date in C6 and today, ignoring the months and years.
=DATEDIF(C7,TODAY(),”md”) 2 Returns the difference of days between the date in C7 and today, ignoring the months and years.
=DATEDIF(C8,TODAY(),”md”) 27 Returns the difference of days between the date in C8 and today, ignoring the months and years.
=DATEDIF(C9,TODAY(),”md”) 7 Returns the difference of days between the date in C9 and today, ignoring the months and years.

Read more: How to Use the Excel DAYS Function


Common Errors with DATEDIF Function

Error When They Show
#Value! When an argument is not of the specified type. For example, when the start_date or end_date argument is a text value, or the unit argument is anything other than the six specific text values.
#Num! When the start_date is less than the end_date

Conclusion

Thus you can use the DATEDIF function of Excel in any formula to count the difference of days, months, or years between any two dates. Do you have any questions? Feel free to inform us.


Further Readings:

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo