# 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:

syntax
`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

ABCD
1Start DateEnd DateYears of TenureResult
21/1/20181/1/2023=DATEDIF(A2,B2,”Y”)5
32/1/20191/1/2023=DATEDIF(A3,B3,”Y”)4
43/1/20201/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

ABCD
21/1/20233/1/2023=DATEDIF(A2,B2,”D”)59
32/1/20234/1/2023=DATEDIF(A3,B3,”D”)59
43/1/20235/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

ABCD
1Start DateEnd DateMonths Until RenewalResult
21/1/202312/1/2023=DATEDIF(A2,B2,”M”)11
32/1/20231/1/2024=DATEDIF(A3,B3,”M”)11
43/1/20232/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

ABCD
1Order DateDelivery DateDays for DeliveryResult
21/1/20231/10/2023=DATEDIF(A2,B2,”D”)9
32/1/20232/11/2023=DATEDIF(A3,B3,”D”)9
43/1/20233/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

ABCD
21/1/20231/1/2024=DATEDIF(A2,B2,”Y”)1
32/1/20231/1/2024=DATEDIF(A3,B3,”Y”)0
43/1/20231/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.

ABCD
1Start DateEnd DateYears of TenureResult
21/1/20181/1/2023=IF(B2=”Still Working”,DATEDIF(A2,TODAY(),”Y”),DATEDIF(A2,B2,”Y”))5
32/1/2019Still Working=IF(B3=”Still Working”,DATEDIF(A3,TODAY(),”Y”),DATEDIF(A3,B3,”Y”))4
43/1/2020Still 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.

ABCD
1Start DateEnd DateDays of ProjectResult
21/1/20233/1/2023=DATEDIF(A2,B2,”D”)59
32/1/20234/1/2023=DATEDIF(A3,B3,”D”)59
43/1/20235/1/2023=DATEDIF(A4,B4,”D”)61
5Total=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.

AB
1Birth DateAge
21/1/1990=DATEDIF(A2,TODAY(),”Y”)
32/1/1985=DATEDIF(A3,TODAY(),”Y”)
43/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”.

ABCD
1Start DateEnd DateMonths Until RenewalResult
21/1/202312/1/2023=IFERROR(DATEDIF(A2,B2,”M”),”Expired”)11
32/1/20231/1/2024=IFERROR(DATEDIF(A3,B3,”M”),”Expired”)11
43/1/20232/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.

ABCD
21/1/20231/1/2024=DATEDIF(MAX(A2,B2),MIN(A2,B2),”Y”)1
32/1/20231/1/2024=DATEDIF(MAX(A3,B3),MIN(A3,B3),”Y”)0
43/1/20231/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.

ABCD
1Last Inspection DateNext Inspection DateDays Until Next InspectionResult
21/1/20231/1/2024=DATEDIF(MAX(A2,B2),MIN(A2,B2),”D”)365
32/1/20232/1/2024=DATEDIF(MAX(A3,B3),MIN(A3,B3),”D”)365
43/1/20233/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.

ABCD
1Date 1Date 2Days BetweenResult
21/1/20231/1/2024=ABS(DATEDIF(A2,B2,”D”))365
32/1/20242/1/2023=ABS(DATEDIF(A3,B3,”D”))365
43/1/20243/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

1. 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.

2. Be careful with the “MD”, “YM”, and “YD” units. These ignore certain parts of the dates, which can lead to unexpected results.

3. Remember to use the current date as the end date if you’re calculating ages.

4. If the start date exceeds the end date, DATEDIF will return a #NUM! Error. You can use the IF function to avoid this.

5. 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.