Calculate Years, Months, and Days Between Two Dates in Excel 2016/365 Without DATEDIF

In Excel 2016 or 365, sometimes you need to calculate the difference between two dates in years, months, and days without using the built-in DATEDIF function. This can be achieved by using a combination of built-in functions such as YEAR, MONTH, and DAY. Here’s a detailed step-by-step guide on how to do this.

Steps to Calculate Differences

Let's assume your two dates are in cells A1 and B1. Let's use the following example:

Start Date A1:2020-01-15

End Date B1:2023-08-14

Follow these steps to calculate the differences:

1. Calculate the Years

YEAR(B1) - YEAR(A1)

2. Calculate the Months

MONTH(B1) - MONTH(A1)   IF(DAY(B1)  DAY(A1), -1, 0)

3. Calculate the Days

DAY(B1) - DAY(A1)   IF(DAY(B1)  DAY(A1), -EOMONTH(A1, 0), 0)

4. Combine the Results

Combine these formulas into a single output to get the total years, months, and days. For example, if you want to see the total output, you can use:

“(Years: [Years], Months: [Months], Days: [Days])”

Example:

For the dates mentioned above (2020-01-15 to 2023-08-14), the combined formula will yield:

Years:3

Months:6

Days:30

Note: Adjust the cell references A1 and B1 as needed for your specific worksheet.

Details and Considerations

Excel’s date/time system treats each day as one unit, starting from Day Zero on the 0th day of January 1900. To find the difference in days between two dates, simply subtract one date from the other. However, the result is displayed as a date/time beginning in 1900, so you need to adjust by subtracting 1900 years, which is relatively straightforward.

Leap Year Issue

One issue with this approach is that Microsoft Excel treats 1900 as a leap year, which it was not. This can cause discrepancies. However, in practical use, the discrepancy is usually negligible.

Flexibility in Calculation

When calculating months and days, it’s important to consider the actual lengths of months and the actual dates involved. For instance:

Between 15 February and 15 June:This spans 3 months (March, April, May) and 30 days (15 February and 15 June), making it 3 months and 30 days. Between 15 February and 15 June in 2021:This also spans 3 months (March, April, May) and 30 days (15 February and 15 June), making it 3 months and 30 days.

While this may seem trivial, the precise method you choose depends on the level of detail you need for your specific use case.

Conclusion

This approach ensures you can calculate the differences between two dates without needing the DATEDIF function. It is flexible and can be adapted to suit various needs, making it a powerful tool for date calculations in Excel 2016 or 365.