Article

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

, 12 Aug 2002
 Rate this:
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;
}```

## Share

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

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

 First Prev Next
 seriel no to DMY rbsshetty 2-Dec-12 1:39
 Alternative JamesHoward972 24-Jan-12 22:33
 Different Alg Memonic 15-Dec-11 7:02
 Re: Different Alg Victor Vogelpoel 15-Dec-11 9:01
 Thanks. sriest 10-Jul-09 10:01
 Error in Excel Serial Date to DMY ?? Harry Adams 17-Sep-08 17:33
 Hi Victor Thank you for providing this code. It has proved a great time saver... I didnot want to have to work this out from scratch. That said, after working with your algorithm, I have to say (at risk of great personal embarrassment) that I am pretty sure there is an error.  I cut and pasted your code and then translated it to Fortran. (OK, I'm a dinosaurbut there's lots of Jurassic era F77 code out here). I found that I was getting an incorrect year value for dates between 1 Mar and end of Dec. At first I thought it was because of bad arithmetic, but I tried it both with floating point and with integer codes, and with various amounts of truncating using INT() and NINT(),but the results were the same. Then I tried coding the entire algorithm in Excel, and I still obtained the same results. Eventually after poking around a bit moreusing a code debugger and looking at the Excel results, I determined that the problem occurs in the calculation of the i variable. I was able to fix it, both in F77 and Excel. I will provide the Fortran here (because it might be useful toanother dinosaur hacker). You will see I have inserted an "IF" statement at the end, which says if the calculated month value is greater than 2 (i.e. March onwards), then subtract one from the year.  To my surprise this seems completely robust. I have tested it for every year from1901 to 2125 (in an Excel spreadsheet) and it produces dates that agree 100%with the date value created internally by Excel. If you are interested I can sendyou the spreadsheet. Hal. *---------------------------------------------------------------------------------          subroutine ExcelDateToDMY(LDate, Y, M, D)         * Translated from 'C' source at http://www.codeproject.com/KB/datetime/exceldmy.aspx?print=true          * Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a* leap year, but Excel/Lotus 123 think it is...          implicit none                  real*8 LDate                  integer*4 Y, M, D                  integer*4 idate,      >               i, j, l, n                  idate = NINT(LDate)   ! Compute integer date                  if (idate .EQ. 60) then               Y = 1900               M = 2               D = 29               return                                       else if (idate .LT. 60) then * Because of the 29-02-1900 bug, any serial date * under 60 is one off... Compensate.             idate = idate + 1            end if * Modified Julian to DMY calculation with an addition of 2415019          l = idate + 68569 + 2415019         n = INT (( 4 * l) / 146097 )         l = l - INT(( 146097 * n + 3) / 4)         i = INT((4000 * ( l + 1 )) / 1461001)         l = l - INT(( 1461 * i ) / 4) + 31         j = INT(( 80 * l ) / 2447)                  D = l - INT (( 2447 * j ) / 80)                  l = INT(j / 11)                  M = 2 + j - ( 12 * l )          Y = 100 * ( n - 49 ) + i + 1 * HA 2008/09/16: Bug fix to original code         if   (M .GT. 2) Y = Y - 1   ! Reduce Year by 1 if Month >= 3          return                  end
 Ported to PHP and fixed Lubosdz 9-Aug-07 0:19
 Works great for date, what about for time? TariAbay 29-Jul-07 20:19
 Re: Works great for date, what about for time? Member 2228136 4-Aug-10 16:26
 YEAR gtown7 17-May-07 4:20
 Going off after Febuvary... vijaicmo 12-Mar-07 0:23
 But how can I know if the cell value is a Date? stanley guan 7-Oct-05 16:51
 Re: But how can I know if the cell value is a Date? Suunil 21-Aug-09 2:21
 Re: But how can I know if the cell value is a Date? Victor Vogelpoel 23-Aug-09 23:51
 Number off by 183 Anonymous 29-Aug-05 4:33
 Re: Number off by 183 Anonymous 29-Aug-05 6:03
 DMYToExcelSerialDate doesn't work for 28th Feb 1900 crusby 9-Feb-05 23:09
 doesn't work in months input > 12 Anonymous 24-Jun-04 12:23
 Re: doesn't work in months input > 12 Victor Vogelpoel 24-Jun-04 22:54
 Thanks - Would like some clarifications jocompto 4-May-04 9:11
 Re: Thanks - Would like some clarifications Victor Vogelpoel 4-May-04 10:29
 date month year in excel Anonymous 3-May-04 4:36
 Re: date month year in excel Victor Vogelpoel 3-May-04 8:44
 Different Results then Excel Alex Baldini 20-Oct-03 12:14
 Ported to PHP Nathan Gasser 24-Aug-03 16:39
 Excel dates in C# Kris Vandermotten 3-Apr-03 1:30
 Re: Excel dates in C# Victor Vogelpoel 3-Apr-03 7:41
 Re: Excel dates in C# VentsyV 29-Nov-07 12:39
 Thanks! Just what I needed Timian Heber 12-Nov-02 8:58
 Re: Thanks! Just what I needed Victor Vogelpoel 12-Nov-02 9:21
 ISO 8601 (International Standard Date Format) Warren Stevens 13-Aug-02 2:46
 Re: ISO 8601 (International Standard Date Format) Warren Stevens 13-Aug-02 3:54
 Last Visit: 31-Dec-99 18:00     Last Update: 20-Aug-14 10:55 Refresh 1