How to Use DATEDIF Function in Excel (2 Ideal Examples)

DATEDIF is one of the most used Excel functions. In this article, you’ll learn how you can use this DATEDIF function efficiently with different criteria and by combining it with other functions in Excel.


Download Practice Workbook

You are welcome to download the practice workbook from the link below.


Introduction to DATEDIF Function

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.
  • However, 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/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. Additionally, 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.
  • 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.

2 Ideal Examples Utilizing Excel’s DATEDIF Function

We can use the DATEDIF function in Excel to count days, months or years between two days. Additionally, it can be used to calculate age from a given birthdate combined with the TODAY function.


1. Calculate Difference Between Two Dates Using DATEDIF Function

Here, we have the Start Date and End Date of six employees of a company. Now, we will show you how you can calculate the difference between these two dates using the DATEDIF function.

In the function, we will insert the value of the unit on the basis of which parameter we want to get.

Calculate Difference Between Two Dates Using DATEDIF Function in Excel


1.1 Determine Number of Days

Firstly, we will show you how you can determine the number of days between two dates using the DATEDIF function.

Steps:

  • In the beginning, select Cell D5 and insert the following formula.
=DATEDIF(B5,C5,"d")
  • Then, press Enter.
  • After that, drag-down the Fill Handle tool to autofill this formula for the rest of the cells.

Determine Number of Days Using Excel DATEDIF Function

Here, in the DATEDIF function, we inserted Cell B5 as start_date, Cell C5 as end_date and “d” as unit.
  • Thus, you can get the days between two dates using this function.


1.2 Count Number of Months

Secondly, here you will find a way to count the number of months between two dates.

Steps:

  • Firstly, select Cell D5 and insert the following formula.
=DATEDIF(B5,C5,"m")
  • Then, press Enter.
  • After that, drag-down the Fill Handle tool to autofill this formula for the rest of the cells.

Count Number of Months Using Excel DATEDIF Function

Similarly, in the function, we inserted the start_date and end_date like the previous example. Then, we inserted “m” as the unit to find the month between the dates.
  • Thus, you can get the month differences between two dates.

Note: When the day of the end_date is less than the day of the start_date, it doesn’t count the last month.


1.3 Determine Number of Years

Similarly, we can determine the number of years between two dates by just changing the unit value.

Steps:

  • To start with, insert the following formula in Cell D5.
=DATEDIF(B5,C5,"y")
  • Then, press Enter and lower the cursor to fill the rest of the data cells.

Determine Number of Years Using Excel DATEDIF Function

Here, the unit argument is “y”. So, the function returns the plane difference of years between the starting date and the ending date.
  • As a result, we will have a difference in years unit.

Note: When the month of the end_date is less than the month of the start_date, it doesn’t count the last year.


1.4 Calculate Number of Days Ignoring Years

In this demonstration, we will use the DATEDIF function to calculate the difference between two dates in days, ignoring years.

Steps:

  • Firstly, choose the Cell D5 and type the following formula.
=DATEDIF(B5,C5,"yd")
  • After that, press Enter and lower the cursor to the last data cell to get results according to the formula.

Calculate Number of Days Ignoring Years Applying Excel DATEDIF Function

Here, to find the days ignoring years between two days, we inserted “yd” as the unit.
  • Consequently, we will find the differences in days after ignoring the years while calculating all the values.

Note: 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.


1.5 Find Out Days Excluding Years & Months

After that, we will show you how you can find out the days excluding the years and months.

Steps:

  • In the beginning, choose Cell D5 and write the following formula down.
=DATEDIF(B5,C5,"md")
  • Then, press the Enter button.
  • Consequently, we will find a difference in days after ignoring both the years and months while calculating.
  • Finally, move the cursor down to the last data cell to get results according to the formula.

Find Out Days Excluding Years & Months Applying Excel DATEDIF Function

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


1.6 Count Months Between Two Dates Ignoring Years

Lastly, in this final example, we will utilize the Excel DATEDIF function to calculate the difference between two dates in months, ignoring years.

Steps:

  • Firstly, choose Cell D5 and type the following formula.
=DATEDIF(B5,C5,"ym")
  • After that, press Enter and lower the cursor to the last data cell to get results according to the formula.

Count Months Between Two Dates Ignoring Years Using Excel DATEDIF Function

Here, to find the months ignoring years between two days, we inserted “ym” as the unit.
  • Consequently, we will find the differences in months after ignoring the years while calculating all the values.

Read more: How to Use DATE Function in Excel (10 Ideal Examples)


2. Apply DATEDIF & TODAY Functions to Count Age in Excel

In the second example, we have a dataset containing the Name and Birthday of some students. Now, we will show you how you can count their age using the DATEDIF and TODAY functions in Excel.

However, the DATEDIF function can also take an array of dates as the start_date argument. Thus, instead of using one date at a time and then dragging the Fill Handle, we will use an Array Formula to enter all the starting dates together and can receive all the values of ages together in this example.

Apply DATEDIF & TODAY Functions to Count Age in Excel


2.1 Determine Years

Firstly, we will determine the age in years. To do that, follow the steps given below.

Steps:

  • In the beginning, insert the following Array Formula in Cell D6.
=DATEDIF(C6:C10,TODAY(),"y")

Inserting Array Formula Using DATEDIF Function in Excel

🔎 How Does the Formula Work?

  • Firstly, in the DATEDIF function, we inserted cell range C6:C10 as start_date.
  • Then, we used the TODAY function to get the date of today as end_date.
  • Finally, we inserted “y” as the unit to get the value in years.
  • Then, press Enter if you are using Excel 365, or else press Ctrl + Shift + Enter for other previous versions of Excel.
  • In either case, you will get the age in years of each student up to today.

Read More: How to Use DAY Function in Excel (3 Ideal Examples)


2.2 Calculate Months

After that, we will count the months ignoring the years as we have counted the age in years above.

Steps:

  • Firstly, to count the months of the ages, enter this Array Formula in Cell E6.
=DATEDIF(C6:C10,TODAY(),"ym")

Here, the function will calculate the months between the start_date (cell range C6:C10) and today ignoring the years, as we inserted “ym” as the unit.
  • Finally, press Enter or Ctrl +Shift + Enter depending on your Excel version.


2.3 Find Out Days

Finally, you can get the age in days ignoring the years and months using both DATEDIF and TODAY functions in Excel going through the steps given below.

Steps:

  • In the beginning, insert the following Array Formula in Cell D6.
=DATEDIF(C6:C10,TODAY(),"md")

Here, the function will find out the days between the start_date (cell range C6:C10) and today ignoring both years and months, as we inserted “md” as the unit.
  • Thus, you can get the required ages in days.

Excel DATEDIF Function

Read More: How to Use the Excel DAYS Function with a Practical Example


Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section


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 Excel DATEDIF function 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

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 the 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