Excel Snippets: Calculate Difference Between Dates

Microsoft ExcelI might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

Most, if not all, of the programming/scripting languages I work with will have a function allowing you to calculate a difference between two dates, but I always have to look them up.

It’s not too difficult in Excel; this formula can be used to work out the number of days difference between the values in cells A2 (start date) and A3 (end date):

=DATEDIF(A2,A3,"D")

The highlighted section can be several values depending on how you want the date difference calculated:

Unit Returns
D The number of days between the supplied dates.
M The number of months between the supplied dates.
Y The number of years between the supplied dates.
MD The number of days between the supplied dates ignoring the months and years.
YM The number of months between the supplied dates with the days and years ignored.
YD The number of days between the supplied dates with the years ignored.

More details and examples on these units are available from the Microsoft Support article which notes they don’t recommend MD, but, to be honest, I don’t see much use for any of the last three listed above.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Leave a Reply

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