loads of useful information, examples and tutorials pertaining to web development utilizing asp.net, c#, vb, css, xhtml, javascript, sql, xml, ajax and everything else...

 



Advertise Here
C-Sharpener.com - Programming is Easy!  Learn Asp.Net & C# in just days, Guaranteed!

Translate an Excel Serial Date into a C# DateTime

by naspinski 7/17/2008 12:24:00 PM

An annoying little 'feature' of Excel is that it stores dates in a strange format

I stole the logic and from Code Project and wrote some code that will give you a DateTime variable if you feed it a Excel serial integer (number of days after 2/29/1900).  Strangely enough, DateTime.Parse("2/29/1900"); throws an error, so the simple solution I thought of: DateTime.Parse("2/29/1900").AddDays(excelInteger); does not work.  But this does:

 

public DateTime ExcelSerialDateToDT(int nSerialDate)
{
    int l = nSerialDate + 68569 + 2415019;
    int n = ((4 * l) / 146097);
    l = l - ((146097 * n + 3) / 4);
    int i = ((4000 * (l + 1)) / 1461001);
    l = l - ((1461 * i) / 4) + 31;
    int j = ((80 * l) / 2447);
    int nDay = l - ((2447 * j) / 80);
    l = (j / 11);
    int nMonth = j + 2 - (12 * l);
    int nYear = 100 * (n - 49) + i + l;

    return DateTime.Parse(nMonth + "/" + nDay + "/" + nYear);
}

 

As long as your date isn't within 60 days of 2/29/1900, this will work perfectly (don't ask why those are screwed up Tongue out).

Tags: ,

c# | excel

Related posts

Comments

2/6/2009 6:57:16 AM

Gido
The Excel serial date represents the number of days since 1/1/1900, NOT 29/2/1900 !! However, Microsoft copied a bug from Lotus123 for compatibility reasons (so Lotus123 users could convert their files to Excel without problems).

The date 29/2/1900 actually never existed, as 1900 was not a leap year. See http://en.wikipedia.org/wiki/Leap_year for more info.

Gido


Comments are closed