**What Is DATEDIF Excel Function?**

The

DATEDIF function in Excelcalculates the difference between two dates. It can be in the form of days, months, or years. TheDATEDIF function in Excelis 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:

**Step 1:** Select the cell to display the output.

**Step 2: **Enter the DATEDIF in excel formula.

The complete formula **=DATEDIF(A2,B2,“M”)**

**Step 3:** 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.

##### Table of contents

###### Key Takeaways

- The DATEDIF in excel is not an inbuilt function in excel. It is used to find the difference between two dates.
- The outcomes returned by the function and YEAR/MONTH formulas are not constantly indistinguishable because they work dependent on various standards.
- Unlike other functions, excel does not suggest the DATEDIF formula.
- To avoid errors, we must enter the values in correct format while calculating the difference between the dates.
- The DATEDIF formula in Excel is also useful while calculating the age.

**DATEDIF() Excel Formula**

The syntax of the **DATEDIF Excel Function** is

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

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

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**

- Select an empty cell for the output.
- Type
**=DATEDIF(**Excel will show no suggestions. - Enter the arguments as in the
**Start_date, End_date, & Unit**. - 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.

**T**he 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**

- The DATEDIF formula in Excel supports only six types of units.
- The function works complexly when we find the difference between days, months, or years.
- The parameters used in the formula may confuse the user.
- 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**

**How to add the DATEDIF function in Excel?**

The DATEDIF function in Excel is added by entering the formula manually. The Excel does not show the suggestion list. To add the procedure, we must know the correct formula.

**How to use the DATEDIF formula in Excel?**

The DATEDIF formula in Excel is used with the following steps:

a. Select an empty cell for the output.

b. Type **=DATEDIF(**

c. Enter the arguments as in the **Start_date**, **End_date**, & **Unit**.

d. Close the parenthesis and press the **Enter** key.

**What does the DATEDIF do in Excel?**

The **DATEDIF in Excel **calculates the number of days, months, or years difference between two dates.

For example, consider the following table showing two dates in columns A and B. 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:**Step 1:** Select the cell to display the output.**Step 2: **Enter the DATEDIF in excel formula.**Step 3: **The unit is ‘D’.

The complete formula is **=DATEDIF(A2,B2,“D”)****Step 4:** Press **Enter** key.

The function has returned the result as 312.

**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 –

## Leave a Reply