Many years ago, I had a weird brush with the vagaries of date arithmetic and Microsoft. Don’t ask me why, but their relationship to date arithmetic is a bit, I don’t know, odd.
Someone needed a data feed from a system I maintained, and in that feed needed the date to be an Excel date serial number. This means the sequence number of the date since 1/1/1900.
So I tweaked the formula in the software of my system slightly to start counting in 1900 – its base was something later, as I recall – and tested it to make sure that it took account of the fact that February 29, 1900 never happened. For the benefit of those few who may not remember this from the year 2000 stuff, only century years evenly divisible by 400 are leap years. All other century years aren’t. As 1900 isn’t, it wasn’t.
Guy on the receiving end calls me up, says the date’s off by one. I say impossible. He tells me to put the date serial number I sent him into Excel, I do (I know, I should’ve done this first time, but, you know, I just ASSUMED Microsoft was doing it right) and lo and behold: it’s off by one day. Excel comes back with yesterday. Blew me away.
I had to sit for a minute before I figured out that the bastards must assume Feb 29, 1900 is a legit date. So I stuck that into Excel, and it popped back with a date serial number. I was shocked, dismayed.
Just last week I had a problem where I had to stick a date into a column of a pre-existing table where the column had already been defined as an integer. So I cast the date to an integer, inserted it into the table. User takes this and puts this in Excel via an external query. It’s off by – drum roll please – two days. TWO DAYS! Please note, gentle reader, that I was using Microsoft SQL Server for the database.
I know, I should’ve tested the resulting number in Excel, but I just ASSUMED Microsoft would sync up their two products. Bad move.
Sigh. Anyway, I figured out that not only was there the Feb 29 problem, there was also the fact that SQL Server starts counting from 0, as in 1/1/1900 is 0 in SQL Server, whereas in Excel it’s 1.
I swear, you can’t make this stuff up. Mind-boggling.
In the now-immortal words of Ronald Reagan…“Trust, but verify.”
I do this with every Excel file/spreadsheet that I create.
That’s pretty fucked up. And I’ve run into similar issues.
In fact, here at the hospital we run one bit of software that was written, I believe, three years before God was born. We download patient data from that and throw it into SQL. As a result, we get some patients who are -23 years old. That is, they were born in 2027. They’re in pretty rough shape for being so young.
Funny thing happened to me a few years ago. I was working on a MS Word app that populated from a SQL database. On the header were things like the document title, author, date, city, etc. Below that was a column of numbers indicating how much money each department made in a given time period. At the bottom was a sum of all earnings.
For some insane reason, Word was screwing up the math. It was constantly calculating that we made $2,000 more than we actually did. We checked and rechecked. We added the numbers by hand. We spent about 3 days trying to figure out where this money had come from. The only thing I could think of was that maybe there was some sort of Y2K bug that crept into the system.
Then it dawned on me – had we run the report a week earlier, before the annual Winter drinking festival, the report would have shown an overage of a mere $1,999.
You sillyhead!