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
(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)
if (nSerialDate == 60)
nDay = 29;
nMonth = 2;
nYear = 1900;
else if (nSerialDate < 60)
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)
if (nDay == 29 && nMonth == 02 && nYear==1900)
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)
Victor is consulting in The Netherlands.
His interests include Windows and web application development using .NET technologies and even some Apache/PHP/MySQL...