DATEDIF Formula in Excel

The DATEDIF formula in Excel returns the number of days, months, and years between two dates. The DATEDIF formula in Excel is a part of Date & Time formulas.

The DATEDIF formula is a hidden or secret formula in Microsoft Excel. Even though you cannot find the DATEDIF formula in the built-in list of formulas, still you can use in old or new Excel versions.

The Excel DATEDIF formula is used to calculate the numbers of complete days, months, and years between start date and end date. It takes two different dates with one unit as arguments to calculate specified type of difference as an output in the period. By using the DATEDIF formula you can calculate the age or years from date of birth. There are many ways to calculate the difference between the two dates which will be explained in this page.

How to use the DATEDIF Formula in Excel?

The article in this webpage describes how to use the DATEDIF formula in Excel with syntax, detailed explanation and examples.

DATEDIF Syntax

=DATEDIF(start_date,end_date,unit)

The arguments for DATEDIF formula are:

• Start Date (required argument) – This is the starting date or initial date of the period.
• End Date (required argument) – This is the ending date or last date of the period.
• Unit (required argument) – This is the type of information or time unit which you want to return. Below are the 6 different types of units, and you can use any one of them:
  • “D” – The number of complete Days between the start and end date.
  • “M” – The number of complete Months between the start and end date.
  • “Y” – The number of complete Years between the start and end date.
  • “MD” – The number of complete Days between the start and end date by ignoring Months & Years.
  • “YM” – The number of complete Months between the start and end date by ignoring Days & Years.
  • “YD” – The number of complete Days between the start and end date by ignoring Years.

DATEDIF Examples

Example 1 (“D” – Days)

In the below DATEDIF formula example, the complete days between the two dates (01 Jan 2050 and 02 Jan 2050) is calculated by using the “D” as unit. The unit in DATEDIF formula is not case sensitive and you can use uppercase or lowercase text. The formula in cell F6 is:

=DATEDIF(C6,D6,”D“)

Example 2 (“M” – Months)

Suppose you want to calculate the number of months between the two dates then you can use the “M” as unit in DATEDIF formula as shown below:

=DATEDIF(C7,D7,”m“)

Example 3 (“Y” – Years)

If you want to calculate the years between the two dates, then use “Y” as unit in DATEDIF formula. In the below example, the formula in cell F9 is:

=DATEDIF(C9,D9,”y“)

Example 4 (“MD” – Days by ignoring Months and Years)

In the below example of DATEDIF formula, the formula in cell F8 calculates the number of days as 1 between 01 Jan 2050 and 02 Feb 2055 by ignoring months (Jan and Feb) and years (2050 and 2055).

And in cell F9, the DATEDIF formula in Excel calculates the number of days as 15 (16 – 1) between 01 Jan 2050 and 16 Jun 2060 by ignoring months (Jan and Jun) and years (2050 and 2060).

=DATEDIF(C8,D8,”md“)

=DATEDIF(C8,D8,”ym“)

Example 5 (“YM” – Months by ignoring Days and Years)

In the below example of DATEDIF formula in Excel, the formula in cell F8 calculates the number of months as 6 (July – Jan or 7 – 1) between the two dates i.e., 01 Jan 2050 and 15 July 2050 by ignoring days (01 and 15) and years (2050 and 2050).

Example 6 (“YD” – Days by ignoring Years)

In the below DATEDIF formula example, the formula in the cell F9 calculates the number of days as 31 between 01 Jan 2050 and 01 Feb 2055 by ignoring the years (2050 and 2055).

=DATEDIF(C9,D9,”yd“)

Important Notes

You must remember the below key points while using the DATEDIF formula in Excel:

• The DATEDIF formula in Excel is a hidden formula.
• The DATEDIF returns #NUM! error when the start date is greater than end date.
• The DATEDIF returns #VALUE! error when the start or end date is not a valid date.
• It may return wrong results under certain scenarios and suggested to use the DATE formula to enter a valid date.
• The dates are stored in sequential serial numbers for calculation. For example: January 1, 1900 is serial number 1 and January 1, 2050 is 54,798 (54,797 days from January 1, 1900).