Click here to Skip to main content
11,639,126 members (64,001 online)
Click here to Skip to main content

Excel serial date to Day, Month, Year and vise versa

, 12 Aug 2002 CDDL 309.5K 30
Rate this:
Please Sign up or sign in to vote.
A speedy algorithm to convert Excel numbers to DMY and vise versa.

Introduction

For a little project of mine, I needed to convert a serial date number to day/month/year (DMY) and vise versa. The serial number came from date field in a converted Paradox database and seemed to be exactly the same as the serial date in Microsoft Excel, hence the article name. After some googling I learned that the Excel serial date is related to Julian date, and found a speedy algorithm to convert these numbers to DMY and vise versa.

Excel Serial Date Number

Now what is an Excel serial date number? 37477 is a serial date number and is the number of days since 1-1-1900. 37477 actually translates to Aug 9, 2002, the date of writing this article.

The number of days since 1-1-1900 isn't that hard to calculate of course, once you know the leap years. Microsoft Excel however contains a bug with its date calculation: it considers 29-02-1900 as a valid date, but 1900 isn't a leap year! 29-02-1900 is not a valid date!

According to the stories, Microsoft decided to duplicate this date bug from Lotus 123, which was the dominating spreadsheet application at the time Excel was being written. This allowed Excel to open Lotus 123 spreadsheets without date problems.

(Note that a serial date number may also contain a fraction that denotes the time. It's actually the percentage of 24 hours, but that's not for this article.)

Mac Excel Serial Date number

Excel on the Apple Mac uses a slightly different Serial Date number base: the number of days since 1-1-1904. That's why Excel also features a "1904 Date System" checkbox in its Options (Calculation tab). I won't go into this futher.

Translating Serial Date Number to DMY

First of all, credit where credit is due. I got the basic algorithm from http://serendipity.magnet.ch/hermetic/cal_stud/jdn.htm. It's about converting a Julian date to DMY using integer calculations. I won't bore you with the theory (following the above hyperlink), but the original Julian has a different base date.

With an addition of a certain number, the algorithm comes close to the Excel serial date. And when the 29-02-1900 issue is handled, we've got an Excel serial date to Day, Month, Year calculation!

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;
}

DMY to Excel Serial Date

Calculate an Excel serial date from Day, Month, Year. The function assumes that the day, month and year are valid date numbers.

int DMYToExcelSerialDate(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 (nDay == 29 && nMonth == 02 && nYear==1900)
        return 60;

    // DMY to Modified Julian calculatie with an extra substraction of 2415019.
    long nSerialDate = 
            int(( 1461 * ( nYear + 4800 + int(( nMonth - 14 ) / 12) ) ) / 4) +
            int(( 367 * ( nMonth - 2 - 12 * ( ( nMonth - 14 ) / 12 ) ) ) / 12) -
            int(( 3 * ( int(( nYear + 4900 + int(( nMonth - 14 ) / 12) ) / 100) ) ) / 4) +
            nDay - 2415019 - 32075;

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

    return (int)nSerialDate;
}

License

This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)

Share

About the Author

Victor Vogelpoel
Software Developer (Senior)
Netherlands Netherlands
Victor is consulting in The Netherlands.

His interests include Windows and web application development using .NET technologies and even some Apache/PHP/MySQL...

You may also be interested in...

Comments and Discussions

 
QuestionDateTime.ToOADate() Pin
Dietmar Schoder29-Jul-14 5:27
professionalDietmar Schoder29-Jul-14 5:27 
Questionseriel no to DMY Pin
rbsshetty2-Dec-12 1:39
memberrbsshetty2-Dec-12 1:39 
QuestionAlternative Pin
JamesHoward97224-Jan-12 22:33
memberJamesHoward97224-Jan-12 22:33 
SuggestionDifferent Alg Pin
Memonic15-Dec-11 7:02
memberMemonic15-Dec-11 7:02 
GeneralRe: Different Alg Pin
Victor Vogelpoel15-Dec-11 9:01
memberVictor Vogelpoel15-Dec-11 9:01 
GeneralThanks. Pin
sriest10-Jul-09 10:01
membersriest10-Jul-09 10:01 
GeneralHi. Thanks for the invaluable contribution. I have found a bug.... Pin
Member 402179718-Nov-08 6:57
memberMember 402179718-Nov-08 6:57 
QuestionError in Excel Serial Date to DMY ?? Pin
Harry Adams17-Sep-08 17:33
memberHarry Adams17-Sep-08 17:33 
GeneralPorted to PHP and fixed Pin
Lubosdz9-Aug-07 0:19
memberLubosdz9-Aug-07 0:19 
QuestionWorks great for date, what about for time? Pin
TariAbay29-Jul-07 20:19
memberTariAbay29-Jul-07 20:19 
AnswerRe: Works great for date, what about for time? Pin
Member 22281364-Aug-10 16:26
memberMember 22281364-Aug-10 16:26 
GeneralYEAR Pin
gtown717-May-07 4:20
membergtown717-May-07 4:20 
GeneralGoing off after Febuvary... Pin
vijaicmo12-Mar-07 0:23
membervijaicmo12-Mar-07 0:23 
QuestionBut how can I know if the cell value is a Date? Pin
stanley guan7-Oct-05 16:51
memberstanley guan7-Oct-05 16:51 
AnswerRe: But how can I know if the cell value is a Date? Pin
Victor Vogelpoel7-Oct-05 22:56
memberVictor Vogelpoel7-Oct-05 22:56 
GeneralRe: But how can I know if the cell value is a Date? Pin
Suunil21-Aug-09 2:21
memberSuunil21-Aug-09 2:21 
GeneralRe: But how can I know if the cell value is a Date? Pin
Victor Vogelpoel23-Aug-09 23:51
memberVictor Vogelpoel23-Aug-09 23:51 
GeneralNumber off by 183 Pin
Anonymous29-Aug-05 4:33
sussAnonymous29-Aug-05 4:33 
GeneralRe: Number off by 183 Pin
Anonymous29-Aug-05 6:03
sussAnonymous29-Aug-05 6:03 
GeneralDMYToExcelSerialDate doesn't work for 28th Feb 1900 Pin
crusby9-Feb-05 23:09
membercrusby9-Feb-05 23:09 
GeneralRe: DMYToExcelSerialDate doesn't work for 28th Feb 1900 Pin
Victor Vogelpoel10-Feb-05 10:10
memberVictor Vogelpoel10-Feb-05 10:10 
Generaldoesn't work in months input &gt; 12 Pin
Anonymous24-Jun-04 12:23
sussAnonymous24-Jun-04 12:23 
GeneralRe: doesn't work in months input > 12 Pin
Victor Vogelpoel24-Jun-04 22:54
memberVictor Vogelpoel24-Jun-04 22:54 
GeneralThanks - Would like some clarifications Pin
jocompto4-May-04 9:11
memberjocompto4-May-04 9:11 
GeneralRe: Thanks - Would like some clarifications Pin
Victor Vogelpoel4-May-04 10:29
memberVictor Vogelpoel4-May-04 10:29 
Generaldate month year in excel Pin
Anonymous3-May-04 4:36
sussAnonymous3-May-04 4:36 
GeneralRe: date month year in excel Pin
Victor Vogelpoel3-May-04 8:44
memberVictor Vogelpoel3-May-04 8:44 
GeneralDifferent Results then Excel Pin
Alex Baldini20-Oct-03 12:14
sussAlex Baldini20-Oct-03 12:14 
GeneralPorted to PHP Pin
Nathan Gasser24-Aug-03 16:39
sussNathan Gasser24-Aug-03 16:39 
GeneralExcel dates in C# Pin
Kris Vandermotten3-Apr-03 1:30
sussKris Vandermotten3-Apr-03 1:30 
GeneralRe: Excel dates in C# Pin
Victor Vogelpoel3-Apr-03 7:41
memberVictor Vogelpoel3-Apr-03 7:41 
GeneralRe: Excel dates in C# Pin
VentsyV29-Nov-07 12:39
memberVentsyV29-Nov-07 12:39 
GeneralThanks! Just what I needed Pin
Timian Heber12-Nov-02 8:58
memberTimian Heber12-Nov-02 8:58 
GeneralRe: Thanks! Just what I needed Pin
Victor Vogelpoel12-Nov-02 9:21
memberVictor Vogelpoel12-Nov-02 9:21 
GeneralISO 8601 (International Standard Date Format) Pin
Warren Stevens13-Aug-02 2:46
memberWarren Stevens13-Aug-02 2:46 
GeneralRe: ISO 8601 (International Standard Date Format) Pin
Victor Vogelpoel13-Aug-02 3:08
memberVictor Vogelpoel13-Aug-02 3:08 
GeneralRe: ISO 8601 (International Standard Date Format) Pin
Warren Stevens13-Aug-02 3:54
memberWarren Stevens13-Aug-02 3:54 
GeneralRe: ISO 8601 (International Standard Date Format) Pin
Victor Vogelpoel13-Aug-02 4:18
memberVictor Vogelpoel13-Aug-02 4:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150728.1 | Last Updated 13 Aug 2002
Article Copyright 2002 by Victor Vogelpoel
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid