How to use Excel DATEDIF Function (Examples + Video)

Excel DATEDIF Function (Examples + Video)

Excel DATEDIF function - Overview

Excel DATEDIF is one of the few undocumented functions (the other ones I know are EVALUATE, FILES, and GET.CELL).

Being undocumented means that you will not find it in the formula list or as a part of the IntelliSense (the prompt that shows up when you type a formula name to show you the matching functions names).

When to use Excel DATEDIF Function

Excel DATEDIF function can be used when you want to calculate the number of years, months, or days between the two specified dates. A good example would be calculating the age.

What it Returns

It returns a numerical value that denotes the number of Years/Months/Days between the two specified dates.  Whether it would be the number of Years, or Months, or Days is determined by the user input (see Input Arguments below).

Syntax

=DATEDIF(start_date,end_date,unit)

Input Arguments

  • start_date: It’s a date that represents the starting date value of the period. It can be entered as text strings in double quotes, as serial number, or as a result of some other function, such as DATE().
  • end_date: It’s a date that represents the end date value of the period. It can be entered as text strings in double quotes, as serial number, or as a result of some other function, such as DATE().
  • unit: This would determine what type of result you get from this function. There are six different outputs that you can get from the DATEDIF function, based on what unit you use. Here are the units that you can use:
    • “Y” – returns the number of completed years in the specified period.
    • “M” – returns the number of completed months in the specified period.
    • “D” – returns the number of completed days in the specified period.
    • “MD” – returns the number of days in the period, but doesn’t count the ones in the Years and Months that have been completed.
    • “YM” – returns the number of months in the period, but doesn’t count the ones in the years that have been completed.
    • “YD” – returns the number of days in the period, but doesn’t count the ones in the years that have been completed.

Additional Notes

  • Excel DATEDIF function is provided for compatibility with Lotus 1-2-3.
  • While typing this function in a cell in Excel, it would NOT show the IntelliSense. It’ll not even show the function’s name as you enter it in a cell. However, it works in all versions of Excel. You need to know the arguments and how to use it.Excel DATEDIF Function does not appear in the intellisense
  • Dates can be entered as text strings within double quotes (for example, “2016/1/15”), as serial numbers (for example, 42384, which represents January 15, 2016, if you’re using the 1900 date system), or as the results of other formulas/functions (for example, DATEVALUE(“2016/1/15”)).

Excel DATEDIF Function – Live Examples

Here are three examples of using the Excel DATEDIF Function.

#1 Calculating the Number of Years Completed between two dates.

Excel DATEDIF Function - Calculating number of years

In the above example, Excel DATEDIF function returns the number of years completed between 01 January 1990 and the current date (which is 14 March 2016 in this example). It returns 26, which is the total number of years completed and ignores the additional months and days after it.

A common use of this could be calculating the age in years.

#2 Calculating the Number of Months Completed between two dates.

Excel DATEDIF Function - calculating number of months

In the above example, Excel DATEDIF function returns the number of months completed between 01 January 1990 and the current date (which is 14 March 2016 in this example).

It returns 314, which is the total number of months completed and ignores the additional days after it.

A good use of this could be calculating the number of months between the start and end dates of projects.

In the above example, it gives the total number of months. But if you want to know the number of months after the total number of completed years, then you need to use YM as the unit argument.

For example, while calculating age in Excel, if you want to know how many years and how many months have elapsed till date, then you can use YM to get the number of months in addition to the years (as shown below).

Excel DATEDIF Function - result of months

#3 Calculating the Number of Days Completed between two dates.

Excel DATEDIF Function - calculating number of days

In the above example, Excel DATEDIF function returns the total number of days completed between 01 January 1990 and the current date (which is 14 March 2016 in this example). It returns 9569, which is the total number of days between the two dates.

If you want to get the number of days between the two dates while excluding the ones from the years that have already completed, you need to use YD as the third argument (as shown in the pic below):

Excel DATEDIF Function - number of days result

In the above example, it returns 72, which is the total number of days after 26 complete years.

If you want to get the number of days between the two dates while excluding the ones from the years and months that have already completed, you need to use MD as the third argument (as shown in the pic below):

Excel DATEDIF Function - Example 9

In the example above, it returns 13, which is the number of days in addition to 26 years and 3 months.

Related Excel Functions:

You May Also Like the Following Excel Tutorials:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)