Excel DATEDIF Function

The DATEDIF function of Excel is among the few undocumented tasks.

You may not be able to see it in the list of formulas or as a part of the IntelliSense prompt that appears whenever you type in a formula name that shows the matching functions’ names.

When can you use the Excel DATEDIF Function?

You may have the DATEDIF function of Excel used whenever you want to calculate the number of days, months, or years between a couple of specified dates.

A great example of this would involve calculating your age.

What it Provides

The DATEDIF function gives a numerical value that says the number of Days/Months/Years between a couple of specified dates.

The user will be the one to determine in his input if it would be the number of Days, Months, or Years. 

Syntax

=DATEDIF(start_date,end_date,unit)

Input Arguments

  • Unit: Here, it determines the kind of result that you can get from this function. From the DATEDIF function, you can get six varied outputs based on the unit that you use. These are the units that you may utilize:
    • “M” – provides the number of months completed in a specific period.
    • “D” – gives the number of days completed in a certain period.
    • “Y” – indicates the number of years completed in a specified period.
    • “YM” – provides the number of months in the period that doesn’t count in the years completed.
    • “YD” – indicates how many days there are in a period that does not include the years completed.
    • “MD” – gives the number of days in a period and doesn’t count the Months and Years completed.
  • end_date: This date represents the period’s end date value that can be entered as a serial number, the result of another function like the DATE (), and text strings in double-quotes.
  • start_date: Here is a date that represents the period’s starting date value. You can enter this as text strings that come with double-quotes due to another function like DATE or as a serial number. 

Additional Notes

  • You can have dates entered as text strings in double-quotes. As an example, “2016/1/15” is 42384 in serial numbers represents January 15, 2016, if you use the 1900 date system. Other formulas or functions have this result: DATEVALUE(“2016/1/15”).
  • The function of Excel’s DATEDIF is provided to be compatible with Lotus 1, 2, and 3.
  • As you type this function in an Excel cell, it will not be showing IntelliSense. It may not even show the name of the function as you have it entered in the cell. Yet, it works in every version of Excel. You need to become aware of how you can use it, including the arguments.

Live Examples of the Excel DATEDIF Function

These are some of the ways people can use Excel’s DATEDIF Function:

#1 The Calculation of the Number of Completed Months between a couple of dates

In this example, the DATEDIF function of Excel provides the number of completed months from January 01, 1990, to March 14, 2016.

Here, 314 is returned as this is the total amount of completed months and doesn’t consider the additional days after it.

The calculation of the number of months from the projects’ beginning and end dates is of great use for this.

This example has the total number of months. However, if you want to know how many after the total amount of completed years, you will have to make YM used as the unit argument.

As an example, as you calculate for age in Excel, you will want to know how many months and how many years elapsed until the date.

You can use YM if you’re going to get how many months aside from the years as shown here:

#2 Calculation of the Number of Completed Days between two dates

In this example, the DATEDIF function of Excel gives the total number of completed days from January 01, 1990, to March 14, 2016.

Here, it returns the total number of days between these two dates, which is 9569.

Whenever you want to have the number of days between a couple of dates and exclude the ones from the years that are already completed, you will have to use the third argument YD:

The example above returns 72 that is the total amount of days after 26 years.

Whenever you want to have the number of days in between these two dates as you exclude the ones from the already-completed months and years, you have to use the third argument, which is MD, as shown here:

In this example, 13 is returned, that is the number of days that are added to 26 years and three months.

#3 Calculation of the Number of Years that are Completed between two dates

In the example above, the DATEDIF function of Excel returns the number of years completed from January 01, 1990, to March 14, 2016.

Here, 26 is produced. It is the total number of completed years that ignores the additional days and months after it.

It is commonly used to calculate the age in years.

Become Well-Versed in Excel Dated If Function with ExcelMaster

Make yourself an Excel expert with us! Allow yourself to become sufficiently knowledgeable about the Excel Dated If Function with ExcelMaster NOW!

Leave a Comment