Search Not Just Numbers

Tuesday 17 September 2013

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.

Excel Expert Course

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

9 comments:

  1. I knew that Excel used a number of days since 1/1/1900 for sdate, but I did not know that excel could not handle a date before then. I tried to format -1 as a date but Excel could not handle it.

    ReplyDelete
    Replies
    1. I know. Surprising isn't it. Not much of an issue for most of us, but I can imagine it being an annoyance to historians!

      Delete
  2. line containing typo:
    17/06/2013 12:00:00 (dd/mm/yyyy hh:mm;ss)

    should read:
    17/09/2013 12:00:00 (dd/mm/yyyy hh:mm:ss)

    ReplyDelete
    Replies
    1. Thanks for that, much appreciated (and well spotted!). I've corrected it now.

      Delete
  3. This is trivial but I'll point it out anyway.
    17/09/2013 (dd/mm/yy)
    17 September 2013 (dd/mmmm/yy)
    17-Sep-2013 (dd-mmm-yy
    All of the above should have yyyy for the year.

    ReplyDelete
    Replies
    1. Sorry. Thanks for pointing that out. I've changed it now. Two errors in one blog, I must have been having a bad day!

      Delete
  4. Thanks for sharing this very informative post in using Excel. Those who use the application on a daily basis will surely find this as helpful. Cheers!

    ReplyDelete
  5. Come on guys, don't be so picky. That sounds like trying to diminish the credit to the poster.
    Instead, say thank you for a very informative post!!

    ReplyDelete
  6. Thanks for the read :D

    ReplyDelete