This article by David Broughton was first published in the May 1999 issue of Hot Key. It is reproduced here by request.
Did you know that Microsoft Works and other Microsoft database and spreadsheet programs store dates as a single number?
If you place a date in a spreadsheet cell in any recognisable format, what is actually stored is a number plus a flag to say that it should be interpreted as a date. The stored number can be seen by changing the format to "number". This removes the date flag so you see the value of the number in place of the date.
The reason a number is used rather than storing the year, month and day separately is so that you can then subtract one date from another to get the number of days between the two dates. The number used is the number of days from 30th December 1899. But any other base date not too far away in time could be used as it cancels out in the subtraction.
You can do this the other way round: place a number in a cell and then change its format to "date" and, provided it is a number in a suitable range (1 to 65379), it will be converted to a date. Actually, you can even convert it to a time. The time is the fractional part of the number interpreted as a fractional part of a day.
The number 65379 corresponds to 30th December 2078, which is 65379 days after 30th December 1899. (That's 44 leap years of 366 days plus 135 ordinary years of 365 days, a total of 179 years.) There's going to be a crisis at the end of 2078 for everyone still using those Microsoft programs. That includes Works, Office, Mini-Office and maybe others. They will not be able to store dates that way any more. It has something to do with a maximum of 16 bits for the integer part. But that is not our problem!
Choosing 30th December 1899 as day zero was a compromise with storage, just as storing two digits for years was a compromise in other programs that are now in crisis with the Millennium Bug. Microsoft database and spreadsheet programs do not suffer from the Millennium Bug and will work successfully up to the end of the year 2078.
One could, of course, have chosen any base date for this purpose if storage was not a problem. Astronomers, for example, use 17 November 1858 as day zero for a date known as MJD = Modified Julian Date. This is actually a modification in smaller numbers of another day number system called the Julian Date which has a zero at noon on 1 January 4713 BC of the Julian Proleptic Calendar. That date is 2,400,000.5 days prior to 0 hours on 17 November 1858.
The Julian Proleptic Calendar, in case you wondered, is the Julian Calendar extended backwards in time following the same rules as the Julian Calendar regarding leap years; i.e. every four years without any exceptions. In this calendar, there is no year zero so that 1 BC is the year prior to 1 AD. This has the unfortunate consequence that leap years prior to 1 AD are not those divisible by four but those years which have a remainder of one when divided by four. Thus the year 4713 BC in this calendar was a leap year.
The days of the week continue without exception in their cycle of seven days and if you work it out, 1 January 4713 BC was a Monday. This date was chosen by an Italian named Joseph Scaliger (1540-1609) as preceding all astronomical events known at that time so that all Julian Dates are positive. The suggestion that this was the day God created the universe can be discounted. It is true that it was a Monday, but the creation time was noon on that day and the earth would not be in existence then, nor the Greenwich meridian which would determine the time of 12 o'clock midday. God is unlikely to have started work at noon anyway.
Astronomers no longer like to count days from noon but prefer the more logical zero hours. Thus, to convert from Julian Date to Modified Julian Date one must subtract 2400000.5. It is also more logical to count from zero than to count from one, but few people seem to accept this fact so we are faced with the first day of the month being day 1. When we are born, we are not one day old, not even one hour old: we are zero days, zero hours old.
So when Microsoft program designers thought of choosing a date for the start of their day numbering system, they thought of the first day of the 20th century, which was 1 January 1900. But this was to be day 1, not day zero. That made day zero 31st December 1899.
Whether they overlooked the fact that the year 1900 is not a leap year or whether they deliberately ignored the complication, I do not know, but their algorithm for working out the day number from the date assumes the year 1900 was a leap year. It was not. Thus all day numbers prior to 1 March 1900 are incorrect by one day. This makes the base date (day zero) 30th rather than 31st December 1899. So if one needs to find out how old someone is in a Microsoft database program who was born in those first two months of the century, they will seem to be a day older than they really are. The 29th February 1900 did not happen but it is included in the calculation of the age, making them appear one day older.
This explains why 30th December 1899 is the base date for Microsoft Works database and spreadsheet programs.
I should add that the unit of time called the day is not a uniform time scale because it is based on the rotation of the earth and the earth's rotation rate is slowing down. But that is another story.
