Open In App

Excel DATEDIF Function

Improve
Improve
Like Article
Like
Save
Share
Report

The DATEDIF is a date function that is generally used to find the number of total days, months, and years between two dates. It takes two dates as an argument with one keyword to specify which type of difference is required as an output. The output of the function will always be an integer form. 

DatedIF Function in Excel

The DATEDIF date function is used to find the difference between two dates(age in simple language). These differences can be in any form years, months, or days. It takes two dates as input with one keyword, where the one is the Start_date and the Second is the End_date, and the keyword is used to determine which type of difference is desired for the output.

Syntax

DATEDIF(start_date,End_date,unit)

Arguments

Datedif is the date function which is the in-built function in Excel.

Start_date: The beginning date from where you want to calculate the difference.

End_date: It is the last date of the period.

Unit: The type of information that you want to be returned.

Time Unit

Result

“Y” or “y”

The difference in complete years

“M” or “m”

The difference in complete months

“D” or “d”

The difference in complete days

“MD” or “md”

The difference in days, ignoring months and years

“YM” or “ym”

The difference in months, ignoring years

“YD” or “yd”

The difference in days, ignoring years.

The time unit to utilize while ascertaining the difference between two dates.

Excel DATEDIF Formula

To find the difference between the two dates in Excel, the important thing is to provide the start and end dates to the DATEDIF function. This can be done in multiple ways as follows:

Cell References

This is the easiest way to make a DATEDIF formula in Excel is to input two valid dates in separate cells and refer to those cells. For example, the following formula counts the number of days between the dates in cells A1 and B1:

=DATEDIF(A1, B1, "d")

Text strings

Excel takes dates as a string for example “1-Jan-2023”, “1/1/2023”, “January 1, 2023”, etc. The dates as text strings enclosed in quotation marks can be typed directly in a formula’s arguments. For instance, this is how you can calculateinto the number of months between the specified dates:

=DATEDIF("1/1/2023", "12/31/2025", "m")

Serial numbers

Microsoft Excel stores each date as a serial number beginning with January 1, 1900, you use numbers corresponding to the dates. But this method is not reliable because date numbering varies on different computer systems. In the 1900 date system, you can use the below formula to find the number of years between two dates, 1-Jan-2023 and 31-Dec-2025:

=DATEDIF(44927, 46022, "y")

Results of other functions

To find out how many days there are between today and 20 May 2025, this is the formula to use.

=DATEDIF(TODAY(), "5/20/2025", "d")

How to Use DATEDIF Function in Excel

There are three formulas to calculate year, month, and days:

For the total number of years = datedif(a3,b3,”y”)

Example 1: The Total Number of Years Between the Two Dates

Step 1: Enter the Start Date and End Date.

Step 2: Now enter the formula in a cell where you want to calculate the age.

Formula to calculate age in years

This formula will give you the total number of years between the two dates.

Age in years

Example 2: The Total Number of Months Between the Two Dates

For a total number of months 

 =datedif(a3,b3, “m”)

Where the first argument is the field and the “m” represents the month.

Step 1: Enter the Start_date and End_date.

Step 2: Enter the formula in a cell where you want the number of months between two dates.

Formula to calculate age in months.

In the below image, you can see the total number of months between the two dates.

age in months.

 Example 3: Number of Days Between Two Dates

For the total number of days   =datedif(a3,b3, “d”) 

 Where the first argument is the field and the “d” represents the day.

Step 1: Enter the Start_date and End_date.

Step 2: Enter the formula in the cell where you want the total number of months.

formula to calculate age in days

Total number of days between the two dates (Start_date and End_date).

age in days.

Arguments 

1. The start date is typed in DDMMYYYY format. You can select the date format in the home tab from the number.

Format of Date

2. The last date or end date is typed in DDMMYYYY format.

3. The “y” for the years unit, “m” for the month unit, and “d” for the days unit.

4. The sixth type of unit (“y”, “m”, “d”, )  (“ym”,”md”, “yd”)  

  • “ym” means the number of months without years,  “md” means the number of days  without years and months,
  • “yd” means the number of days without years

Example 4: Find the Number of Months Excluding Days and Years

By this function, we can find our age (in years months, and days) from our date of birth to the current date. Suppose any person’s birth date is 26 January 1969 and the current date is 05 May 2020 (exact in years, months, and days ).

Step 1: Enter the DOB of the Person and the Current date(You can use Today() instead of the current date).

Step 2: Now Use the formula (=datedif(dob,current_date) in a cell where you want the age of the person. 

Formula to calculate age in years and month.

In the image given below, we can see the total number of months excluding years and days.

age in years and months.

Example 5: Total Number of Days Excluding Months and Years

Step 1: Enter the DOB and current date of the person.

Step 2: Enter the formula (=datedif(DOB, Current_date) in a cell, where you want the age in days excluding years and months.

Formula to calculate age in dd-mm--yy

You can see the output in the below image, the number of days excluding years and months.age in dd-mm-yy

Frequently Asked Questions

What is the DATEDIF function in Excel?

DATEDIF is the Built- in date function which is used to calculate the the difference between two given dates in days, months and years.

It takes two dates with one keyword as an argument. 

What are the advantages of using the DATEDIF function?

1. It is easy to use and understand.

2. This function is useful to see the difference between two dates in Years, Months, Days.

What are the Disadvantages of using the DATEDIF function?

  • Excel supports only six types of intervals in DATEDIF function.
  • It cannot give the output for so many units like the first day of the week.
  • May lead to confusion because of too many parameters in a single formula.
  • It might give wrong output if correct date format has not been used.



Last Updated : 20 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads