Tuesday, September 13, 2005

Excel ... aaaargh ...

I am working for a client that is using a lot of automated B2B scenarios. These include, next to normal EDI (Edifact and X12), ordering by mail. My client’s client sends orders as mail attachments. This has been working for quite some time, until recently, I got a phone call. Apparently, something was wrong in _my_ code because the requested delivery date was wrong by a day.
The requested delivery date is, well, quite special. It is an integer indicating the number of days that passed since 01/01/1900. “Why?” you might ask. No idea. But hey, no problem, I can count.
So this is the code that was parsing the “number of days passed since 01/01/1900” to a real date:

            DateTime baseDate = DateTime.ParseExact("01011900", "ddMMyyyy", null);

            int days = int.Parse(numDays);

            return baseDate.AddDays(days - 1).ToString("yyyyMMdd", null);


I didn’t quite understand why this code could lead to a wrong requested delivery date. So I asked the client’s client how I was supposed to parse this date. They provided me with a sample in Excel. Indeed the date was wrong by a day, but why. I created the following table in Excel:
numDateExcelmyCode
101/01/190001/01/1900
202/01/190002/01/1900
36429/12/190030/12/1900
36530/12/190031/12/1900
36631/12/190001/01/1901
1000018/05/192719/05/1927
2000003/10/195404/10/1954
This is what I call: "The AHA effect". When looking at the above table, the only thing you can say is: "AHA", and after a while "WTF". Anyway. It seems that our nifty spreadsheer cannot count. It "thinks" that 1900 is a leap year, but this is not the case. That's why my code's parsed date was different by a day.
Apparently Microsoft knows about this bug. It's not even a bug, it's a feature.
Really? I call it plain wrong. It seems that Lotus-1-2-3 was having this bug to, so Microsoft copied the bug for compatibility reasons.
OpenOffice does not have the above feature.
Because my client was not expecting any orders before 28/02/1900, I changed the above code to:

            DateTime baseDate = DateTime.ParseExact("01011900", "ddMMyyyy", null);

            int days = int.Parse(numDays);

            return baseDate.AddDays(days - 2).ToString("yyyyMMdd", null);


Plain stupid, I know, but what can I do ...

No comments: