DATEDIF In Excel

The DATEDIF is a date function used to find the difference between two given dates. Now, these differences can be in years, months, or days. It takes two dates as an argument and one keyword to specify which type of difference is desired for the output. The output for this function is an integer, and the method to use this function is as follows =DATEDIF(start_date, end_date, unit). However, this function is compatible with Excel 2000 and previous versions of Excel.

For example, suppose we have 1/1/2013 as a start date in column A5 and 31/7/17 as an end date in column B5. We want to know the difference between the two days in column C. In this scenario, we can use the DATEDIF function in Excel. The formula we can use as:

=DATEDIF(A5, B5,”C”)

As a result, it will give us 1,672 days.

• DATEDIF function calculates the difference between two dates in years, months, or days. Use =DATEDIF(start_date, end_date, unit) and specify the unit of difference using a keyword. Output is an integer. It only works with Excel 2000 and earlier versions.
• Ensure the end date is after the start date when using DATEDIF to calculate months accurately in Excel. Use the correct unit of time in formulas. DATEDIF function needs specific arguments.
• Don't use "MD" units to avoid negative values. Use the correct data format when calculating value differences. The DATEDIF formula in Excel can show a #VALUE! Error for unrecognized dates.

DATEDIF(start_date, end_date, unit)

1. Start_date – the initial date of the period you want to calculate.
2. Unit: The time unit to utilize while ascertaining the difference between two dates.
3. End_date - the ending date of the period.

Number Of Units Available

• #1 – Y (Years): Y is used in a formula to find the number of full years between beginning and end dates.
• #2 – M (Months): used in a formula to find the number of entire months between the dates.
• #3 – D (Days): D is used in a formula to see the number of days between the beginning and end dates.
• #4 – MD (Days barring years and months): MD is used in a formula to find the date contrast in days, disregarding months and years.
• #5 – YD (Days barring years): YD is used in a formula to find the date contrast in days, ignoring years.
• #6 – YM (Months barring days and years): YM is used in a formula to find the date contrast in months, ignoring days and years.

Example #1 – Number Of Days Between The Two Dates

Example #2 – Count The Number Of Days Ignoring Years

• Step 1: In this example, we will use the following data to find the no. of days, ignoring years.

• Step 2: First, we must enter the DATEDIF formula in the first row and use “YD” as a unit in a formula.

• Step 3: Then, we need to press the “Enter” key, and we will find the difference in days between the two dates.

• Step 4: If we drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.

Example #3 – Count The Number Of Months Between The Two Dates

• Step 1: We will use the following data to find the number of months between the two dates.

• Step 2: Next, we will enter the DATEDIF formula in the first row and use “M” as a unit in a formula

• Step 3: Then, we should press the "Enter" key and find the difference in days between the two dates in excel.

• Step 4: If we drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.

Example #4 – Find The Days Excluding Years And Months

• Step 1: We will use the following data to find the days, excluding years and months.

• Step 2: We must enter the DATEDIF formula in the first row, and use “MD” as a unit in a formula.

• Step 3: We will find the difference in days between the two dates. We will press the "Enter" key.

• Step 4: If we drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.

Example #5 - Find The Days Excluding Days And Years

• Step 1: We will use the following data to find the days, excluding days and years.

• Step 2: First, we must enter the DATEDIF Formula in the first row and use "YM" as a unit in a formula.

• Step 3: Then, press the "Enter" key, and we will find the days, excluding days and years.

• Step 4: If we drag the formula, we will get the result for all the dates. So, Please find the same in the figure shown below.

Example #6 – Find The Years

• Step 1: Suppose we use the following data to find the years between the two dates.

• Step 2: We must enter the DATEDIF formula in the first row first and use “Y” as a unit in a formula.

• Step 3: Next, we need to press the "Enter" key, and we will find the years between the two dates.

• Step 4: If we drag the formula, we will get the result for all the dates. Therefore, please find the same in the figure shown below.

1. The DATEDIF function in Excel is easy to use if we want to find the difference between the two dates.
2. We can use this formula to see the difference between several days, weeks, or months.
3. The DATEDIF function helps us to know the difference between the six parameters.
4. The DATEDIF function is so simple. In many cases, we can use it if the unit is a day, year, or month.
5. The user needs this function only when it requires more control.

1. The DATEDIF formula in Excel supports only six types of intervals or can help find the difference between only six types of units.
2. It cannot control so many types of units like the first day of the week.
3. Sometimes this function leads to complexity if we only want to find the difference between days, months, or years.
4. Sometimes it may lead to confusion because of many parameters used in one formula.
5. The DATEDIF formula in Excel only gives you the formula if you use the correct date format in excel.

Some of the important points to remember while DATEDIF function is not working are:

• For the DATEDIF formula in Excel to figure months accurately, the end date should be greater than the start date; otherwise, the formula may give the  #NUM error.
• The outcomes returned by DATEDIF and YEAR/MONTH formulas are not constantly indistinguishable because they work dependent on various standards.

For example, the Excel DATEDIF work restores the number of whole calendar months between the dates, while the YEAR/MONTH equation works on months' numbers.

• When Excel does not recognize the date given, it results in the #Value! Error because Excel cannot understand the data argument.

• We must always select the correct unit in a formula.
• Excel cannot help us give instructions to fill the DATEDIF formula like other functions.
• It is also recommended not to use "MD" as units because sometimes it gives a negative value.
• Always make sure to use data format while calculating the difference between the values.
• The DATEDIF formula in Excel is also useful while calculating the age.

This article has been a guide to DATEDIF in Excel. We discuss using the DATEDIF function and Excel examples, and downloadable Excel templates here. You may also look at these useful functions in Excel: -

• VBA Date Function
• Excel Insert Date
• Concatenate Date in Excel
• Today's Date in Excel