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

Question

How do I convert from excel serial date to a .NET date time?

For example 39938 is 05/05/2009.

score 198 · Answer 1 · edited Sep 10 '12 at 12:22

198

I find it simpler using FromOADate method, for example:

DateTime dt = DateTime.FromOADate(39938);

Using this code dt is "05/05/2009".

edited Sep 10 '12 at 12:22

Omar

16,329
10
48
66

answered Apr 12 '10 at 14:56

Narcís Calvet

7,304
5
27
47

3

This also handles dates with time values (floating point in the underlying data format), which the accepted solution does not. – Derek Slager Jun 17 '10 at 19:56
@nullptr However, it has a precision of only 1 millisecond. You can get a much higher precision if you care. See [my answer elsewhere](http://stackoverflow.com/a/13922172/1336654). – Jeppe Stig Nielsen Dec 17 '12 at 21:15
1

For my PowerShell peepz: `[DateTime]::FromOADate($serialDate)` – Kellen Stuart Oct 23 '17 at 16:45

Joel Coehoorn · Accepted Answer · 2020-05-15T18:37:41.683

107

Where 39938 is the number of days since 1/1/1900?

In that case, use the framework library function DateTime.FromOADate() .
This function encapsulates all the specifics, and does bounds checking.

For its historical value, here is a possible implementation:

(C#)

public static DateTime FromExcelSerialDate(int SerialDate)
{
    if (SerialDate > 59) SerialDate -= 1; //Excel/Lotus 2/29/1900 bug   
    return new DateTime(1899, 12, 31).AddDays(SerialDate);
}

VB

Public Shared Function FromExcelSerialDate(ByVal SerialDate As Integer) As DateTime
    If SerialDate > 59 Then SerialDate -= 1 ' Excel/Lotus 2/29/1900 bug
    Return New DateTime(1899, 12, 31).AddDays(SerialDate)
End Function

[Update]:
Hmm... A quick test of that shows it's actually two days off. Not sure where the difference is.

Okay: problem fixed now. See the comments for details.

edited May 15 '20 at 18:37

answered Apr 07 '09 at 20:37

Joel Coehoorn

399,467
113
570
794

1

I know 1 day is for Lotus having a bug about 02/29/1900 it had it as a day that doesn't exist. I don't know what the other day is. – David Basarab Apr 07 '09 at 20:42
VBA dates (Excel) start at 12/30/1899 – DJ. Apr 07 '09 at 20:42
It works if you subtract 2, but it fails for serial dates of at least below 60 (02/29/1900) and the second sneak day in there. – David Basarab Apr 07 '09 at 20:43
3

I think you get 2 days too much with this conversion, i.e. the epoch date would have to be moved back to DateTime(1899, 12, 30). This is due to Excel's leap year bug I assume. – Dirk Vollmar Apr 07 '09 at 20:43
3

Oops, everyone else was faster ;-) And yes, the bug originally comes from Lotus as Joel explained: http://www.joelonsoftware.com/items/2006/06/16.html – Dirk Vollmar Apr 07 '09 at 20:45
You also need to do a check for any number under 60, if it is under 60 do not subtract 1 because you are not in the bug yet. If it is over you must subtract one from the date because of the 02/29/1900 bug. – David Basarab Apr 07 '09 at 20:48
Wow, even a fix for the first to months of the beginning of time (in Excel universe) is included! Impressed :-) – Dirk Vollmar Apr 07 '09 at 20:52
1

You add to 12/31/1899, so that 1/1/1900 is day #1 rather than day #0. After that it's the Lotus leap year thing. – Joel Coehoorn Apr 07 '09 at 20:56
31

-1 for duplicating what is already solved in the .NET Framework (DateTime.FromOADate). – May 01 '12 at 09:22
4

For more info on FromOADate, see http://msdn.microsoft.com/en-us/library/system.datetime.fromoadate.aspx – JDB Nov 05 '12 at 17:46
5

While I agree that the FROMOADate method is better, I still found this answer informative. – VoteCoffee Jul 14 '14 at 14:26
Wonderful solution by Joel Coehoorn. It also saved my lot of time. – Brijesh Kumar Tripathi Dec 12 '18 at 10:17
Another thing that's worth mentioning is that it's possible to have an Excel file that uses **1/1/1904** as a starting date instead of the above expected _1/1/1900_. This can be set with **MS Excel -> Options -> Advanced -> Use 1904 date system**. For more info see [Differences between the 1900 and the 1904 date system in Excel](https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system). – Mario Z Dec 24 '19 at 10:16

score 2 · Answer 3 · answered Apr 13 '16 at 09:15

2

For 39938 do this: 39938 * 864000000000 + 599264352000000000

864000000000 represents number of ticks in a day 599264352000000000 represents number of ticks from the year 0001 to the year 1900

answered Apr 13 '16 at 09:15

Aistis Raudys

111
1
3

score 1 · Answer 4 · edited Apr 12 '10 at 14:44

void ExcelSerialDateToDMY(int nSerialDate, int &nDay, 
                          int &nMonth, int &nYear)
{
    // Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a
    // leap year, but Excel/Lotus 123 think it is...
    if (nSerialDate == 60)
    {
        nDay    = 29;
        nMonth    = 2;
        nYear    = 1900;

        return;
    }
    else if (nSerialDate < 60)
    {
        // Because of the 29-02-1900 bug, any serial date 
        // under 60 is one off... Compensate.
        nSerialDate++;
    }

    // Modified Julian to DMY calculation with an addition of 2415019
    int l = nSerialDate + 68569 + 2415019;
    int n = int(( 4 * l ) / 146097);
            l = l - int(( 146097 * n + 3 ) / 4);
    int i = int(( 4000 * ( l + 1 ) ) / 1461001);
        l = l - int(( 1461 * i ) / 4) + 31;
    int j = int(( 80 * l ) / 2447);
     nDay = l - int(( 2447 * j ) / 80);
        l = int(j / 11);
        nMonth = j + 2 - ( 12 * l );
    nYear = 100 * ( n - 49 ) + i + l;
}

Cut and Paste of someone elses talents...

Ian Brown

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

4 Answers4

Linked

Related