In this article, we will demonstrate how to use the DATEDIF function to calculate the difference between two particular dates, whether in days, months, or years.
Download Practice Workbook
Introduction to the DATEDIF Function
The DATEDIF function determines how many days, months, or years there are between two dates. It takes as arguments an initial date, a final date, and a unit that defines whether the difference will be in days, months or years.
- Generic Syntax
DATEDIF(start_date,end_date,unit)
- Argument Description
| ARGUMENT | REQUIREMENT | EXPLANATION |
|---|---|---|
| start_date | Required | The initial date or the beginning of a particular period. |
| end_date | Required | The final date of the particular period. |
| unit | Required | The type of information the formula should return. |
6 Suitable Examples Using the DATEDIF Function in Excel
Example 1 – Calculating the Difference Between Two Dates in Days
Steps:
- Click on cell D5 and enter the following formula:
=DATEDIF(B5,C5,"d")- Press Enter.
- The difference between the dates is returned in days unit.
- Drag the Fill Handle down to fill the rest of the cells in the column.
Read More: How to Use DATE Function in Excel (8 Examples)
Example 2 – Calculating the Difference Between Two Dates in Months
Steps:
- Click on cell D5 and enter the following formula:
=DATEDIF(B5,C5,"m")- Press Enter.
We have the difference in months unit.
- Drag the cursor down to fill the rest of the cells in the column.
Read More: How to Use Excel MONTH Function (6 Examples)
Example 3 – Calculating the Difference Between Two Dates in Years
Steps:
- Click on cell D5 and enter the following formula:
=DATEDIF(B5,C5,"y")- Press Enter.
The difference in years is returned.
- Drag the cursor down to Auto-fill the rest of the cells in the column.
Read More: How to Use YEAR Function in Excel (5 Examples)
Similar Reading
- Excel Current Time Formula (7 Suitable Examples)
- How to Use TIMEVALUE Function in Excel (4 Examples)
- Use TIME Function in Excel (8 Suitable Examples)
- How to Remove Time from Date in Excel (6 Approaches)
Example 4 – Estimating Differences in Days, Ignoring Months and Years
Now we will use the DATEDIF function to calculate the difference between two dates in days, ignoring the months and years.
Steps:
- Click on cell D5 and enter the following formula:
=DATEDIF(B5,C5,"md")- Press Enter.
The difference in days after ignoring the months and years is returned.
- Drag the cursor down to the last cell in the column.
Read More: How to Use DAYS Function in Excel (7 Examples)
Example 5 – Calculating the Difference in Months, Ignoring the Years
Steps:
- Click on cell D5 and enter the following formula:
=DATEDIF(B5,C5,"ym")- Press Enter.
The difference in months after ignoring the years is returned.
- Drag the cursor down to the last cell in the column.
Example 6 – Calculating the Difference in Days, Ignoring the Years
Steps:
- Click on cell D5 and enter the following formula:
=DATEDIF(B5,C5,"yd")- Press Enter.
The difference in days after ignoring the years is returned.
- Drag the cursor down to the last cell in the column.
Related Articles
- How to Use TODAY Function in Excel (6 Easy Examples)
- How to Use EDATE function in Excel (5 Simple Examples)
- Use DATEVALUE Function in Excel (6 Suitable Examples)
- How to Use the Excel DAYS Function with a Practical Example
HAVE YOU ENCOUNTERED any problems using MD?
Hi SANDEEP KOTHARI!
I have not encountered any problems while using MD. If you face any problems, please send us your Excel file.
Thank you for being with us.
Regards
Md. Abdur Rahim Rasel
Exceldemy Team
IS THERE A CONCISE WAY TO EXPRESS THE DATEIF BETWEEN 2 DATES TO CALCULATE THE Y, YM, MD TO BE REPRESENTED IN A CELL AS xxYRS xxMOS xxDYS WITHOUT DOING IT ALL IN 4 DIFFERENT CELLS TO GET
1 FOR YR
2 FOR YM
3 FOR MD
AND THEN A SEPARATE CELL AS
‘=TEXT(D11,”0″)&” yrs “&TEXT(D12,”0″)& ” mos ” &TEXT(D13+1,”0″) &” dys”‘ ???
Hello Fatima,
Yes! You can use a single formula to calculate and display the difference between two dates in the “xx YRS xx MOS xx DYS” format without using multiple helper cells. Try this formula:
=DATEDIF(A1,B1,”y”) & ” YRS ” & DATEDIF(A1,B1,”ym”) & ” MOS ” & DATEDIF(A1,B1,”md”) & ” DYS”
Explanation:
DATEDIF(A1, B1, “y”) → Returns the number of full years.
DATEDIF(A1, B1, “ym”) → Returns the number of months after counting full years.
DATEDIF(A1, B1, “md”) → Returns the number of days after counting full months.
This formula will give you a single result like: “5 YRS 8 MOS 12 DYS”
Regards
ExcelDemy