From DateTime to Int ? (1 = 01/01/1900, Excel)

Question

Possible Duplicate:
How do I convert an Excel serial date number to a .NET DateTime?

I would like to find an easy way to convert DateTime to Int where 01/01/1900 is 1 (which is used in Excel's xmls).

You may find your answer on this CodeProject Article link: [Excel serial date to Day, Month, Year and vise versa][1]: [1]:http://www.codeproject.com/Articles/2750/Excel-serial-date-to-Day-Month-Year-and-vise-versa — Furqan Safdar, Oct 13 '12 at 10:45

score 1 · Answer 1 · edited Oct 13 '12 at 11:29

1

That's a built-in conversion:

int excelDays = (int)(yourDate.ToOADate());

Beware that 0 is not 1/1/1900, it is 12/31/1899. Screwy due to a shortcut taken by Lotus 123 programmers who were trying to avoid having to deal with leap year rules. 1900 wasn't a leap year. Having to be compatible with Lotus was a requirement back when Excel started, everybody used Lotus 123 back then. A good war story about this bug was written up by Joel Spolsky, the CEO of StackExchange in this blog post. Relevant part is "It's a bug in Excel!" I exclaimed.

edited Oct 13 '12 at 11:29

Hans Passant

922,412
146
1,693
2,536

answered Oct 13 '12 at 10:43

Daniel Mošmondor

19,718
12
58
99

1

Google "excel lotus bug" to see why this is not correct. – Hans Passant Oct 13 '12 at 10:47
I just answered question as it is :) – Daniel Mošmondor Oct 13 '12 at 11:12
1

That's not the SO way, is it? I fixed it the SO way. – Hans Passant Oct 13 '12 at 11:19
:) SO way is 'answer the question as the question is correctly asked' – Daniel Mošmondor Oct 13 '12 at 11:24
I've tested this (ToOADate()) with a few date: in 9/9/2012 = 41161 correct, 5/5/1935 = 12909 correct, 1900/1/1 = 2, incorrect. In Excel it's 1. 31/12/1900 = 366 ... according to Windows Calendar, February 1900 had only 28 days so year 1900 must have had 365. – TTT Oct 13 '12 at 11:35

TTT · Accepted Answer · 2012-10-13T12:12:30.100

And here is my suggestion after compiling ideas from here above:

            private Nullable<int> ToInt1900ForExcel(DateTime DT)
    {
        if (DT.Ticks < 599266080000000000)//Below 1900-1-1, Excel has some problems representing dates, search it on the web.
            return null;
        if (DT.Ticks < 599317056000000000)//Excel reproduced a bug from Lotus for compatibility reasons, 29/02/1900 didn't actually exist.
            return (int)(DT.ToOADate()) - 1;
        return (int)(DT.ToOADate());
    }

or if you prefer (negative values and zero won't work fine in Excel)

           private int ToInt1900ForExcel(DateTime DT)
    {
        if (DT.Ticks < 599317056000000000)//Excel reproduced a bug from Lotus for compatibility reasons, 29/02/1900 didn't actually exist.
            return (int)(DT.ToOADate()) - 1;
        return (int)(DT.ToOADate());
    }

From DateTime to Int ? (1 = 01/01/1900, Excel)

2 Answers2