DATEDIF Excel Function

What Is DATEDIF Excel Function?

The DATEDIF function in Excel calculates the difference between two dates. It can be in the form of days, months, or years. The DATEDIF function in Excel is not present under the Function Library group of the Formula tab. But still, we can make use of the function with the correct syntax.

For example, consider the below table with the start date and end date in columns A and B, respectively. We need to use the below steps to find the difference using DATEDIF in excel. The steps used to find the difference using DATEDIF in Excel are as follows:

• Select the cell to display the output.
• Enter the DATEDIF in excel formula. The complete formula =DATEDIF(A2,B2,“M”)
• Press Enter key.
• We can see that the function has returned the value as ‘1’, indicating the difference between the two dates

Likewise, we can find the difference between dates using DATEDIF in Excel.

DATEDIF() Excel Formula

The syntax of the DATEDIF Excel Function is

Unlike most other functions, all the arguments of the DATEDIF function are mandatory. The arguments are:

• start_date: It denotes the starting date of the period.
• end_date: It indicates the period’s end date.
• unit: It is the time unit used to establish between to dates.

Unit	Explanation
“y”	The difference in the complete years.
“m”	The difference is in the complete month.
“d”	The difference in the complete days.
“md”	The difference is in the complete days ignoring months and years.
“ym”	The difference is in the complete months, ignoring days and years.
“yd”	The difference is in the complete days, ignoring years.

How To Use DATEDIF Excel Function?

Enter in the worksheet manually

1. Select an empty cell for the output.
2. Type =DATEDIF( Excel will show no suggestions.
3. Enter the arguments as in the Start_date, End_date, & Unit.
4. Close the parenthesis and press the Enter

Let us take an example to understand this Excel function.

The below table shows the start date and end date in columns A and B, respectively. But, first, we need to find the difference between the two dates using DATEDIF in Excel.

The steps to calculate DATEDIF in excel are as follows:

Step 1:  Select an empty cell to display the output. We have selected cell C2 in this case.

Step 2:  Next, start by entering DATEDIF in excel formula.

Step 3: Enter the start date as A2 in cell C2.

Step 4: Enter the end date as B2 in cell C2.

Step 5: Enter the time unit as ‘D’ in cell C2.

The complete formula is =DATEDIF(A2,B2,“D”).

Step 6: Press Enter key.

The number of days between two dates is shown in C2 as 31 days.

Likewise, we can obtain results using DATEDIF in excel.

Examples

Let us have a look at the following examples to understand the function better.

Example – 1 – Number Of Days Between The Two Dates

The following table shows the start date and end date. We need to calculate the days between the two dates using Excel DATEDIF.

In the table,

• Column A shows the start date.
• Column B contains the end date.

The steps to calculate DATEDIF in Excel are as follows:

Step 1:  Select an empty cell to display the output. We have selected cell C2 in this case.

Step 2:  Next, start by entering the formula in cell C2.

Step 3: Enter the start date as A2 in cell C2.

Step 4: Enter the end date as B2 in cell C2.

Step 5: Enter the time Unit as ‘D’ in cell C2.

The complete formula is =DATEDIF(A2,B2,“D”).

Step 6: Press Enter key.

The number of days between two dates is shown in C2 as 366 days.

Likewise, we can calculate the number of days using DATEDIF in excel.

Example – 2 – Count The Number Of Days Ignoring Years

The below table shows the start dates and end dates. We need to calculate the number of days between the two dates while ignoring years, using DATEDIF in Excel.

In the table, the data is reflected as below: –

• Column A lists the start date.
• Column B shows the end date.

The steps to calculate the number of days while ignoring years are as follows:

Step 1:  Select an empty column to display the output. We have selected column C in this case.

Step 2:  Next, start by entering the formula in cell C2.

Step 3: Enter the start date as A2 in cell C2.

Step 4: Enter the end date as B2 in cell C2.

Step 5: Enter the time unit as ‘YD’ in cell C2; the function will calculate the number of days, ignoring years.

The complete formula is =DATEDIF(A2,B2,“YD”).

Step 6: Press Enter key.

The number of days between two dates, ignoring years, is shown in C2 as ‘1 day’.

Step 7: Drag the formula downwards till cell C7 to obtain the results instantly.

Similarly, we can find results with DATEDIF in Excel.

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

The below table lists the start dates and end dates in columns A and B, respectively. We need to calculate the months between two dates using DATEDIF in Excel.

The steps used to calculate the months are as follows:

Step 1:  Select an empty column to display the output. We have selected column C in this case.

Step 2:  Next, start by entering the formula in cell C2.

Step 3: Enter the start date as A2 in cell C2.

Step 4: Enter the end date as B2 in cell C2.

Step 5: Enter the time unit as ‘M’ in cell C2; the function will calculate the number of months.

The complete formula entered is =DATEDIF(A2,B2,“M”).

Step 6: Press Enter key.

The number of months between two dates is shown in C2 as 18 months.

Step 7: Drag the formula downwards till cell C7 to obtain the results instantly.

Similarly, we can use the DATEDIF function in excel to obtain the results.

Example – 4 – Find The Days Excluding Years And Months

The following table shows the start dates in column A and the end dates in column B. We need to calculate the days between the two dates, excluding years and months, using the DATEDIF in Excel.

The steps to calculate the number of days, excluding years and months, are as follows:

Step 1:  Select an empty column to display the output. We have selected column C in this case.

Step 2:  Next, start by entering the formula in cell C2.

Step 3: Enter the start date as A2 in cell C2.

Step 4: Enter the end date as B2 in cell C2.

Step 5: Enter the time unit as ‘MD’ in cell C2; the function will calculate the number of days, excluding months and years.

The complete formula entered is =DATEDIF(A2,B2,“MD”).

Step 6: Press Enter key.

The number of days between two dates is shown in C2 as “22 days”.

Step 7: Drag the formula downwards till cell C7.

Similarly, we can use the function to get the number of days between the mentioned dates in excel.

Example – 5 – Find The Days Excluding Days And Years

The below table shows the start dates and end dates in columns, A and B, respectively. We need to calculate the days between two dates, excluding days and years, using the DATEDIF in Excel.

The steps to calculate the number of days are as follows:

Step 1:  Select an empty column to display the output. We have selected column C in this case.

Step 2:  Next, start by entering the formula in cell C2.

Step 3: Enter the start date as A2 in cell C2.

Step 4: Enter the end date as B2 in cell C2.

Step 5: Enter the time unit as ‘MD’ in cell C2; the function will calculate the number of days, excluding days and years.

The complete formula is =DATEDIF(A2,B2,“YM”).

Step 6: Press Enter key.

The calculated number of days between two dates is shown in C2 as 1 day.

Step 7: Drag the formula downwards till cell C7 to obtain the results instantly.

Likewise, we can obtain the results with DATEDIF in excel.

Example – 6 – Find The Years

The below table shows the start and end dates. But, first, we need to calculate the years using the DATEDIF function in Excel.

In the table,

• Column A lists the start dates.
• Column B contains the end dates.

The steps to calculate the years between the mentioned years are as follows:

Step 1:  Select an empty column to display the output. We have selected column C in this case.

Step 2:  Next, start by entering the formula in cell C2.

Step 3: Enter the start date as A2 in cell C2.

Step 4: Enter the end date as B2 in cell C2.

Step 5: Enter the time unit as ‘Y’ in cell C2; the function will calculate the number of years.

The complete formula is =DATEDIF(A2,B2,“Y”).

Step 6: Press Enter key.

The number of days between two dates is shown in C2 as 12 years.

Step 7:  Drag the formula downwards till cell C7.

Likewise, we can obtain the difference in years instantly.

DATEDIF Function Not Working

1. The DATEDIF formula in Excel supports only six types of units.
2. The function works complexly when we find the difference between days, months, or years.
3. The parameters used in the formula may confuse the user.
4. The DATEDIF formula in Excel only gives the formula if we use the correct date format in excel.

Important Things To Note

• The DATEDIF in excel function finds the number of days between the two dates.
• It can also calculate the years or months, as the system must consider an average day for every month and year.
• All months do not have the same number of days, so that the calculation might differ.
• The #NUM! error occurs when the start_date exceeds the end_date.
• The #VALUE! error occurs when the date argument is invalid.
• The unit must be correct in a formula to avoid errors.

Frequently Asked Questions

Download Template

This article must help understand DATEDIF in Excel with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to DATEDIF in Excel. Here we discuss how to use Datedif formula with examples and downloadable excel template. You can learn more from the following articles –

• DATE Excel Function
• Calculate Age In Excel
• Not Equal To In Excel