MS Excel Trick: Calculate Difference Between Two Dates

General Tips-And-Tricks

The formula below subtracts one date (ketp in B1) from another (kept in B2) and divides by 365. The INT function then eliminates the decimal part of the result:

=INT((B2-B1)/365)

Although it servers the purpose to a large extent but if there’s a matter of accuracy, it fails because it divides by average number of days in the year.

To get the accurate result, here’s another version:

=DATEDIF(B1,B2,”y”) & ” Years, “& DATEDIF(B1,B2,”ym”) & ” Months, ” & DATEDIF(B1,B2,”md”) & ” Days”

This formula returns a text string, like the following:
33 years, 8 months, 17 days

It returns the accurate results even if both the dates fall in the same year or in the same month and the year.

Give it a whirl!!!

Leave a Reply

Your email address will not be published. Required fields are marked *