DATEDIF Function in Microsoft Excel
Part 1: Introduction
Definition and Purpose: The DATEDIF function in Excel calculates the number of days, months, or years between two dates. It’s beneficial in formulas where you must calculate an age or a period.
Syntax & Arguments:
DATEDIF(start_date, end_date, unit) Arguments:
start_date: The first or starting date of a given period. Dates may be entered as text strings within quotation marks (e.g., “2001/1/30”), as serial numbers (e.g., 36921, which represents January 30, 2001, if you’re using the 1900 date system), or as the results of other formulas or functions (e.g., DATEVALUE(“2001/1/30”)).
end_date: The last, or ending, date of the period.
Unit: The type of information that you want to be returned. Here are the possible values:
- “Y”: The number of complete years in the period.
- “M”: The number of whole months in the period.
- “D”: The number of days in the period.
- “MD”: The difference between the days in start_date and end_date. The months and years of the dates are ignored.
- “YM”: The difference between the months in start_date and end_date. The days and years of the dates are ignored.
- “YD”: The difference between the days of start_date and end_date. The years of the dates are ignored.
Return Value: The DATEDIF function returns the difference between two dates based on the unit specified.
Remarks: If the start_date exceeds the end_date, the result will be #NUM!.
Part 2: Examples
Let’s look at examples of using the DATEDIF function in business. For these examples, we’ll use hypothetical data.
Example 1: Calculating Employee Tenure
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Years of Tenure | Result |
| 2 | 1/1/2018 | 1/1/2023 | =DATEDIF(A2,B2,”Y”) | 5 |
| 3 | 2/1/2019 | 1/1/2023 | =DATEDIF(A3,B3,”Y”) | 4 |
| 4 | 3/1/2020 | 1/1/2023 | =DATEDIF(A4,B4,”Y”) | 3 |
In this example, we calculate the number of complete years an employee has worked based on their start and end dates.
Example 2: Calculating Days Until Project Deadline
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Days Until Deadline | Result |
| 2 | 1/1/2023 | 3/1/2023 | =DATEDIF(A2,B2,”D”) | 59 |
| 3 | 2/1/2023 | 4/1/2023 | =DATEDIF(A3,B3,”D”) | 59 |
| 4 | 3/1/2023 | 5/1/2023 | =DATEDIF(A4,B4,”D”) | 61 |
In this example, we calculate the number of days until a project deadline from the current date.
Example 3: Calculating Months Until Contract Renewal
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Months Until Renewal | Result |
| 2 | 1/1/2023 | 12/1/2023 | =DATEDIF(A2,B2,”M”) | 11 |
| 3 | 2/1/2023 | 1/1/2024 | =DATEDIF(A3,B3,”M”) | 11 |
| 4 | 3/1/2023 | 2/1/2024 | =DATEDIF(A4,B4,”M”) | 11 |
In this example, we’re calculating the number of months until a contract needs to be renewed.
Example 4: Calculating Days Between Order and Delivery
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order Date | Delivery Date | Days for Delivery | Result |
| 2 | 1/1/2023 | 1/10/2023 | =DATEDIF(A2,B2,”D”) | 9 |
| 3 | 2/1/2023 | 2/11/2023 | =DATEDIF(A3,B3,”D”) | 9 |
| 4 | 3/1/2023 | 3/12/2023 | =DATEDIF(A4,B4,”D”) | 11 |
In this example, we calculate the number of days between an order date and the delivery date.
Example 5: Calculating Age of Inventory
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Received Date | Current Date | Age of Inventory | Result |
| 2 | 1/1/2023 | 1/1/2024 | =DATEDIF(A2,B2,”Y”) | 1 |
| 3 | 2/1/2023 | 1/1/2024 | =DATEDIF(A3,B3,”Y”) | 0 |
| 4 | 3/1/2023 | 1/1/2024 | =DATEDIF(A4,B4,”Y”) | 0 |
In this example, we’re calculating the age of inventory based on the date it was received and the current date.
Example 6: Calculating Employee Tenure with IF Function
In this example, we’ll calculate an employee’s tenure in years. If the employee is still working, we’ll use the current date as the end date.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Years of Tenure | Result |
| 2 | 1/1/2018 | 1/1/2023 | =IF(B2=”Still Working”,DATEDIF(A2,TODAY(),”Y”),DATEDIF(A2,B2,”Y”)) | 5 |
| 3 | 2/1/2019 | Still Working | =IF(B3=”Still Working”,DATEDIF(A3,TODAY(),”Y”),DATEDIF(A3,B3,”Y”)) | 4 |
| 4 | 3/1/2020 | Still Working | =IF(B4=”Still Working”,DATEDIF(A4,TODAY(),”Y”),DATEDIF(A4,B4,”Y”)) | 3 |
Example 7: Calculating Total Days of Multiple Projects with SUM Function
In this example, we’ll calculate the total days of multiple projects.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Days of Project | Result |
| 2 | 1/1/2023 | 3/1/2023 | =DATEDIF(A2,B2,”D”) | 59 |
| 3 | 2/1/2023 | 4/1/2023 | =DATEDIF(A3,B3,”D”) | 59 |
| 4 | 3/1/2023 | 5/1/2023 | =DATEDIF(A4,B4,”D”) | 61 |
| 5 | Total | =SUM(C2:C4) | 179 |
Example 8: Calculating Age with TODAY Function
In this example, we’ll calculate a person’s age based on their birth date and the current date.
| A | B | |
|---|---|---|
| 1 | Birth Date | Age |
| 2 | 1/1/1990 | =DATEDIF(A2,TODAY(),”Y”) |
| 3 | 2/1/1985 | =DATEDIF(A3,TODAY(),”Y”) |
| 4 | 3/1/2000 | =DATEDIF(A4,TODAY(),”Y”) |
Example 9: Calculating Months Until Contract Renewal with IFERROR Function
In this example, we’ll calculate the months until a contract needs to be renewed. If the contract has already expired, we’ll return “Expired”.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Months Until Renewal | Result |
| 2 | 1/1/2023 | 12/1/2023 | =IFERROR(DATEDIF(A2,B2,”M”),”Expired”) | 11 |
| 3 | 2/1/2023 | 1/1/2024 | =IFERROR(DATEDIF(A3,B3,”M”),”Expired”) | 11 |
| 4 | 3/1/2023 | 2/1/2024 | =IFERROR(DATEDIF(A4,B4,”M”),”Expired”) | 11 |
Example 11: Calculating Age of Inventory with MAX Function
In this example, we’ll calculate the age of inventory based on the date it was received and the current date. We’ll use the MAX function to ensure we don’t get a negative number if the received date is in the future.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Received Date | Current Date | Age of Inventory | Result |
| 2 | 1/1/2023 | 1/1/2024 | =DATEDIF(MAX(A2,B2),MIN(A2,B2),”Y”) | 1 |
| 3 | 2/1/2023 | 1/1/2024 | =DATEDIF(MAX(A3,B3),MIN(A3,B3),”Y”) | 0 |
| 4 | 3/1/2023 | 1/1/2024 | =DATEDIF(MAX(A4,B4),MIN(A4,B4),”Y”) | 0 |
Example 12: Calculating Days Until Next Inspection with MIN Function
In this example, we’ll calculate the days until the next inspection based on the last and next inspection dates. We’ll use the MIN function to ensure we don’t get a negative number if the next inspection date is past.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Last Inspection Date | Next Inspection Date | Days Until Next Inspection | Result |
| 2 | 1/1/2023 | 1/1/2024 | =DATEDIF(MAX(A2,B2),MIN(A2,B2),”D”) | 365 |
| 3 | 2/1/2023 | 2/1/2024 | =DATEDIF(MAX(A3,B3),MIN(A3,B3),”D”) | 365 |
| 4 | 3/1/2023 | 3/1/2024 | =DATEDIF(MAX(A4,B4),MIN(A4,B4),”D”) | 365 |
Example 13: Calculating Days Between Two Dates with ABS Function
In this example, we’ll calculate the absolute number of days between two dates, regardless of which date is earlier.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date 1 | Date 2 | Days Between | Result |
| 2 | 1/1/2023 | 1/1/2024 | =ABS(DATEDIF(A2,B2,”D”)) | 365 |
| 3 | 2/1/2024 | 2/1/2023 | =ABS(DATEDIF(A3,B3,”D”)) | 365 |
| 4 | 3/1/2024 | 3/1/2023 | =ABS(DATEDIF(A4,B4,”D”)) | 365 |
In this example, we’re using the ABS function to return the absolute value of the difference between two dates, ensuring the result is always a positive number.
Part 3: Tips and Tricks
Remember that DATEDIF calculates “complete” units. For example, if the start date is 1/1/2023 and the end date is 12/31/2023, DATEDIF will return 0 years because a year has not passed.
Be careful with the “MD”, “YM”, and “YD” units. These ignore certain parts of the dates, which can lead to unexpected results.
Remember to use the current date as the end date if you’re calculating ages.
If the start date exceeds the end date, DATEDIF will return a #NUM! Error. You can use the IF function to avoid this.
DATEDIF is a “compatibility” function included in Excel for compatibility with Lotus 1-2-3. It’s not documented in all versions of Excel, so don’t be surprised if you can’t find it in the Excel help files.