## DATEDIF Function in Excel

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 arguments and one keyword to specify which type of difference is desired for the output, the output for this function is 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.

### Syntax

**DATEDIF(start_date, end_date, unit)**

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

#### Number of Units Available

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

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked

For eg:

Source: DATEDIF in Excel (wallstreetmojo.com)

### How to use the DATEDIF Function in Excel? (with Examples)

#### Example #1 – **Number of days between the two dates**

**Use the following data to find the no. of days between 2 dates.****Enter DATEDIF formula in the first row, use D as a unit in a formula.****Press Enter, you will find the difference in days between the two dates.****Now, if we will drag the formula, we will get the result for all the dates. Please find the same in the figure shown below.**

#### Example #2 – **Count the number of days ignoring years**

**Step 1:**Use the following data to find the no. of days ignoring years.

**Step 2:**Enter DATEDIF Formula in the first row, use YD as a unit in a formula.

**Step 3:**Press Enter, you will find the difference in days between the two dates.

**Step 4:**Now, if we will 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:**Use the following data to find a number of months between the two dates.

**Step 2:**Enter DATEDIF Formula in the first row, use M as a unit in a formula

**Step 3:**Press Enter, you will find the difference in days between the two dates in excelDates In ExcelThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read more.

**Step 4:**Now, if we will 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:**Use the following data to find the days, excluding years and months.

**Step 2:**Enter DATEDIF Formula in the first row, use MD as a unit in a formula.

**Step 3:**Press Enter, you will find the difference in days between the two dates.

**Step 4:**Now, if we will 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:**Use the following data to find the days excluding days and years

**Step 2:**Enter DATEDIF Formula in the first row, use YM as a unit in a formula.

**Step 3:**Press Enter, you will find the days, excluding days and years.

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

#### Example #6 – **Find the years.**

**Step 1:**Use the following data to find the years between the two dates

**Step 2:**Enter DATEDIF Formula in the first row, use Y as a unit in a formula

**Step 3:**Press Enter, you will find the years between the two dates.

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

### Advantages

- DATEDIF in excel is very easy to use if you find the difference between the two dates.
- If you want to find the difference between a number of days, week, or month, use this formula.
- DATEDIF helps you find the difference between the six parameters.
- DATEDIF is so simple because of simplicity, and it can be used in many of the cases if the unit is a day, year, or month.
- The user needs this function only when it requires more control.

### Disadvantages

- DATEDIF formula in excel supports only six types of intervals or able to help to find the difference between only six types of units.
- It cannot control so many types of units like the first day of the week.
- Sometimes this function leads to complexity if you only want to find the difference between the day, month, or years.
- Sometime it may lead to confusion because of so many parameters used in one formula.
- DATEDIF formula in excel only gives you the formula if you are using the correct date format in excelDate Format In ExcelThe date format in Excel can be changed either from the “number format” of the Home tab or the “format cells” option of the context menu.read more.

**Things to Remember**

- For the DATEDIF formula in excel to figure months accurately, the end date should be greater than the start date; otherwise, the formula gives the error the #NUM error.
- The outcomes returned by DATEDIF and YEAR/MONTH formulas are not constantly indistinguishable in light of the fact that they work dependent on various standards. The Excel DATEDIF work restores the number of complete calendar months between the dates, while the YEAR/MONTH equation works on months’ numbers.
- Always select the correct Unit in a formula.
- Excel will not help you to give instruction to fill DATEDIF formula like other functions.
- It is also recommended that don’t 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.
- DATEDIF formula in excel is also useful while you are calculating the age.
- When Excel does not recognize the date given, it results in the #Value!error because excel won’t understand the data argument.

### Recommended Articles

This has been a guide to DATEDIF in Excel. Here we discuss how to use the DATEDIF function along with excel examples and downloadable excel templates. You may also look at these useful functions in excel –

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion

## Leave a Reply