An Interesting Problem

A while back a client got in touch with me.

Sarah had downloaded a file of financial transactions from her bank and loaded them into a Microsoft Excel workbook. When she looked at the dates against each transaction, something strange had happened. Every date was exactly four years and one day earlier than it should have been. What on earth was going on?

How are Dates Stored?

This turns out to be all to do with the way Excel stores dates behind the scenes.  In the following illustration, I’ve typed the same date twice (which just happens to be my birthday!).  The version on the left is formatted to look like a Date, the one on the right is formatted as a General number:

 

When you enter (say) 30/06/2016, Excel doesn’t actually store those characters.  Instead, it stores a Serial Number which represents that date.  The Serial Number for 30th June 2016 is 42551.

Where does that number come from?  It’s the number of days since 1st January 1900 – honest!

You can prove that by trying it the other way around.  Type 10 into an Excel worksheet and then format it to look like a date.  You’ll get 10/01/1900.  Entering 10000 will give you 18/05/1927, while 20000 equates to 03/10/1954.

Why is it done that way?

The developers of spreadsheet programs decided to use this system so that arithmetic with dates was easier to organise – [incidentally, this is a concept which pre-dates Microsoft Excel].

If the dates were stored using the characters that you type (i.e. 30/06/2016), a formula to calculate an entry for the following day would be quite complex.   Issues like “thirty days hath September” would have to be taken into account.

When a date is stored as a Serial Number, all you have to do is add 1 to that number.  Microsoft Excel has built-in coding to convert the new number into the correct date:

 

What has this got to do with Sarah’s problem?

There is a little known option in Microsoft Excel called – Use 1904 date system.  When this is turned on, Serial numbers are calculated starting from 1st January 1904.  [Why have two difference systems? – I’ll explain that later in this blog]

If you type 30/06/2016 into a workbook which is set to use that system, the serial number stored is 41089:

 

There is no problem in having two systems, as long as you are consistent.  The option affects an entire workbook so copying dates between sheets in the same workbook is fine.

However, try copying a cell containing 30/06/2016 from a workbook using the 1904 system, to a workbook using the 1900 system.  What you are actually copying is the Serial Number 41089.  In the 1900 system this number gets translated to 29/06/2012.

This is exactly four years and one day earlier than it should be – which was precisely Sarah’s problem!

A Bit of History

The earliest versions of Excel for the Mac only had the 1904 date system – (in fact hearing that Sarah was using an Apple Mac computer was my first clue towards solving the problem).  Later versions gave you the option of choosing which system you wanted to use, as do Windows PC versions.

The Mac designers remembered that the year 1900 wasn’t a Leap Year.  The rules say that the first year of a new century is only a Leap Year if it is divisible by 400 – the year 2000 was one, 2100 won’t be.  By staring their Serial Numbers at the 1st January 1904, they could avoid any complications arising from that fact.

Interestingly, Lotus 1-2-3 (the market leading spreadsheet program of the mid-1980’s) allowed you to enter 29/02/1900 as a valid date.  Its developers forgot about the 400 year rule.

This gave Microsoft a dilemma when creating Excel.  On the one hand they wanted the program to be accurate.  On the other hand they wanted the two programs to work the same way – (so that they could lure the maximum number of users to move from Lotus 1-2-3 to Excel).

They decided to deliberately leave the “bug” in place.  If you format an Excel date to show the day of the week, it gets it wrong for dates before 1st March 1900.

How did I get the date 01/03/1900 to display Thursday?  Find out about that (and a few more tricks for manipulating dates) in our blog Microsoft Excel – Working with Dates.