I’m in the process of writing my Pulitzer worthy book “An Excel Analyst’s guide to Access”. During the writing process, chapters get reviewed by a team of editors – one of which is a Technical Editor.
The Technical Editor on this book is Dick Kusleika (Daily Dose of Excel). Kusleika is one of the better technical editors I’ve worked with. I think his accountant’s detail-oriented brain lends itself nicely to tech editing.
Quick Joke: Did you hear about extroverted accountant? He looks down at your shoes.
.
During his editing, Kusleika pointed out that that the serial dates in Access actually start at December 31, 1899 (not January 1, 1900 like in Excel).
In Excel, every possible date starting from January 1, 1900 is stored as a serial number. For example, January 1, 1900 is stored as 1; January 2, 1900 is stored as 2; and so on. This system of storing dates as serial numbers is commonly called the 1900 system.
.
In Access, it’s different.
Run this Select Query in Access and you’ll get the number 2.
SELECT CLng(#1/1/1900#) AS Expr1
Apparently December 31, 1899 is 1.
How have I missed that?!!
.
I’m assuming Access is different because the SQL team actually wrote the original Jet engine that Access runs on. Their date system didn’t have to be based on an old Lotus product like in Excel.
.
I haven’t quite gotten my brain around the implications of this difference between the serial dates in Excel and Access. Is there any scenario where this difference would affect date-based analyses?
“January 12, 1900 is stored as 2″, wouldn’t it be stored as 12?
Jared: You caught a typo. If fixed it. Thanks.
Mike, SELECT CLng(#2/29/1900#) AS Expr1 will return an error in Access as there was not really a Feb 29, 1900, while it returns 60 in Excel. After that the two days are back in sync.
To me, the only time this would ever hit you is if you were trying to analyze dates pre-1900. And probably then only if you were exporting from Access to Excel for analysis, but I could be wrong there.
Hopefully I’ll never have to worry about it.
And not to forget to 1904 one too! Often if I’m working with time, I process the serial number so that could catch me out. I didnt know about the 1899 thing in Access good catch Dick!
When someone says “We’ll pay you to point out all of Mike Alexander’s failings”, I say “Sign me up!”. I wish tech editing paid more because I really do enjoy it.