From the Not Just Numbers blog:
I hope all of my readers in the UK are enjoying our current heatwave.
The focus here in the UK for weeks now has been whether Andy Murray could finally win the Wimbledon Men’s Singles title, 77 years after the last Brit (Fred Perry) won it on 3rd July 1936. A feat Murray achieved magnificently on Sunday (7th July 2013) – well done Andy.
In a blatant attempt to be topical, I thought I would explain this week, how to calculate the elapsed time between two dates in Excel.
NB: To avoid any confusion, all dates are shown using the format most commonly used in the UK, i.e. dd/mm/yyyy.
Dates in Excel are stored as numbers, starting with 1 being 1/1/1900, 2 being 2/1/1900, etc.
The date of Sunday’s final (7/7/2013) is stored in Excel as 41,462. You can see this by typing the date in Excel and changing the number format back to General.
[I don’t intend to cover times in this post, but it is worth noting that where 1 is a whole day, decimals mark the time in that day. Technically 41,462 is midnight on the morning of the 7/7/2013 – 41,462.5 would be 12 noon on that day.]
Given that the date is held in Excel as a number (representing the number of days since 1/1/1900), then if cell A1 holds the date of Fred Perry’s Wimbledon win (3/7/1936) and A2 holds the date of Andy Murray’s win (7/7/2013), to calculate the elapsed number of days between the two we simply need to subtract one from the other, so the following formula would work:
=A2-A1
…the answer being 28,128 days.
In many applications this is what is required, e.g. days an invoice has been outstanding, however in this example years might be a more appropriate unit of measure. We can do this in one of two ways, which are subtly different:
We can deduct the year of the first date from the year of the second date, using the YEAR function to pull out the year:
=YEAR(A2)-YEAR(A1)
…returning 77, i.e. 2013-1936
This is probably the most appropriate approach for our purposes as Wimbledon is an annual competition, and we would not be concerned with the timing during the year.
An alternative approach would be to use the DATEDIF function which can calculate the total COMPLETE years that have elapsed between the two dates.
DATEDIF has the following Syntax:
=DATEDIF(Start date,End date,Interval)
where Interval, can be “d” (complete days), “m” (complete months) or “y” (complete years).
Interval can also be “ym”, “yd” or “md” being complete months excluding years, complete days excluding years, or complete days excluding months (and years) respectively.
In our example we simply need:
=DATEDIF(A1,A2,”y”)
…this also returns 77, however as this measures COMPLETE years, the answer would have been 76 if this year’s final had been, say, 1st July. This is the reason that, for our example, I would recommend the previous approach.
Here’s to hoping that the gap until the next British champion is more appropriately measured in days!
Click here for our our exclusive offer on Online Excel Training
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.