Excel Tip: How Excel handles dates and time

My wife did the Great North Run half marathon on Sunday in 2 hrs 42 minutes and 23 seconds, almost the same time as she took last time she did it when she was 17, 25 years ago. Well done to everyone who did the Great North Run yesterday, I'm definitely doing it next year - once I've lost a few stone!

I mention this partly because I am one proud husband today, partly because she has raised nearly £500 for a cause very close to our hearts, but mostly because this gave me the inspiration for today's post.

I've never really done a post specifically on the interesting way that Excel handles time.

I've touched on it in other posts, but not specifically covered it, so let's do it now.

Let us start with the dawn of time. Now I realise that in parts of the US in particular this can be a contentious subject, but for Excel it is quite simple.

Time begins on 1st January 1900!

Excel cannot handle dates before this, and all subsequent dates are measured from this.

A date in Excel is stored as a number. 1 being the 1st January 1900, 2 being the 2nd January 1900, etc. Today's date in Excel terms (17th September 2013) is 41,534.

You may have noticed that zeros formatted as dates in Excel appear as 00/01/1900, obviously depending on the format chosen (I have used the conventional UK format dd/mm/yyyy). This is the day before the dawn of time, the 0th of January 1900.

Following this convention of 1 representing a day, times are represented as fractions of a day, so 0.5 is 12 noon and 41,534.5 is 12 noon today.

Every time you see a time or date in Excel, it is a serial number on this scale. What you see is determined by the format applied to the cell. If you change the cell format to Number, you can see the serial number behind the date or time.

I won't go into number formats in detail here (I'll cover that in a future post), but 41,534.5 could appear as any of these for example:

17/09/2013 (dd/mm/yyyy)
17 September 2013 (dd/mmmm/yyyy)
17-Sep-2013 (dd-mmm-yyyy)
12:00:00 (hh:mm:ss)
17/06/2013 12:00:00 (dd/mm/yyyy hh:mm:ss)

The benefit of this serial number approach is that you can apply normal formulae to dates and times. For example you can deduct one time or date from another to establish the elapsed time between them.

Hopefully that gives you an idea of what is underlying dates and times in Excel.

I hope you find it useful.



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