Add your own alternative version
Stats
316.6K views 30 bookmarked
Posted
12 Aug 2002

Comments and Discussions




i have one questin i have entered a 40406 in textbox how do i convert it to aug 16 2010





How about this alternative:
public static double GetDateTimeAsDouble(DateTime value, bool use1904DateSystem)
{
return (value  (use1904DateSystem ? new DateTime(1904, 1, 1) : new DateTime(1899, 12, 30))).TotalDays;
}
Inverting method can be easily implemented with DateTime.AddDays method.
Or you can use this Excel C# / VB.NET library that automatically converts System.Double dates to DateTime.





Hi, i had to implement the same function in my app, i've tried to use ur alg in FLEX (AS3) but with no sucess, so i builted my own.
function DMYtoSerialDate(nDay:int, nMonth:int, nYear:int):int
{
var arrMonthsDays:Array = new Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
var nSerialDate:int= 1;
var mCounter:int = 0;
for(var i:int = 1901; i <= nYear; i++)
{
if(i%4==0)
nSerialDate+=366;
else
nSerialDate+=365;
}
while(mCounter < nMonth1)
{
nSerialDate += arrMonthsDays[mCounter];
mCounter++;
}
nSerialDate+=parseInt(nDay);
return nSerialDate;
}





Expensive algorithm and flawed for the leap years...
VictorV





Thank you so much for the code.It helped me a lot.





I have run a dummy test. Celled function to convert serial to year, month, day then called straight the other function to convert back those to serial. 1900/02/28 Caused a problem as the output/input was not the same.
Instead of
if (nSerialDate < 60) {
nSerialDate;
}
One needs to write:
if (nSerialDate <= 60) {
nSerialDate;
}
Here is the complete test code:
#include "stdafx.h"
#include <iostream>
void ExcelSerialDateToDMY(int nSerialDate, int &nDay,
int &nMonth, int &nYear)
{
if (nSerialDate == 60)
{
nDay = 29;
nMonth = 2;
nYear = 1900;
return;
}
else if (nSerialDate < 60)
{
nSerialDate++;
}
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;
}
int DMYToExcelSerialDate(int nDay, int nMonth, int nYear)
{
if (nDay == 29 && nMonth == 02 && nYear==1900)
return 60;
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)
{
nSerialDate;
}
return (int)nSerialDate;
}
int _tmain(int argc, _TCHAR* argv[])
{
for (int i=0; i<500001; ++i) {
int nDay = 0; int nMonth = 0; int nYear = 0;
ExcelSerialDateToDMY(i, nDay, nMonth, nYear);
int nSerial = DMYToExcelSerialDate(nDay, nMonth, nYear);
if (i != nSerial) {
std::cout << "ERROR ::: " << "nSerial>" << nSerial << " Year>" << nYear << " nMonth>" << nMonth << " nDay>" << nDay << std::endl;
}
}
return 0;
}
</iostream>





Hi Victor
Thank you for providing this code. It has proved a great time saver... I did not 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 dinosaur but 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 more using 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 to another 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 from 1901 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 send you 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 29021900. 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 29021900 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





Yes, it is true, second function returns wrong results. I reckon, that formula is not quite correct.
Here are 2 fixed PHP functions for conversion date to EXCEL serialDATE and vice versa. Both functions have been well tested for dates between 01/01/1900  31/12/2099.
// Excel/Lotus 123 have a bug with 29021900. // 1900 is not a leap year, but Excel/Lotus 123 think it is... function excel_D2DMY($days) { if ($days <1) return ""; if ($days == 60) { return array('day'=>29,'month'=>2,'year'=>1900); } else { if ($days < 60) { // Because of the 29021900 bug, any serial date // under 60 is one off... Compensate. ++$days; } // Modified Julian to DMY calculation with an addition of 2415019 $l = $days + 68569 + 2415019; $n = floor(( 4 * $l ) / 146097); $l = $l  floor(( 146097 * $n + 3 ) / 4); $i = floor(( 4000 * ( $l + 1 ) ) / 1461001); $l = $l  floor(( 1461 * $i ) / 4) + 31; $j = floor(( 80 * $l ) / 2447); $nDay = $l  floor(( 2447 * $j ) / 80); $l = floor($j / 11); $nMonth = $j + 2  ( 12 * $l ); $nYear = 100 * ( $n  49 ) + $i + $l; $ret = array('day'=>$nDay,'month'=>$nMonth,'year'=>$nYear); return $ret; } }
/** * @desc Returns Excel serialDate constant calculated from gregorian date * original javascript found @ http://wwwmacho.mcmaster.ca/JAVA/JD.html * ported to PHP by XL * * tested compatibility with EXCEL dates between 01/01/1900..31.12.2099, * year given eventually as 0 (=1900)..199 (=2099) * adds also days if supplied argument more than real number of days * in particular month (same applies to months) * as for years bellow 1900: algorhytm is not exact, therefore limitation * for years 1900  2099 */ function excel_DMY2D($d, $m, $y, $uh=0, $um=0, $us=0) { if($y<1900){ if($y<0  $y>199){ echo ' !!! Invalid year ['.$y.'], must be between 1900  2099 or 0  199 !!!'; return false; }else{ $y += 1900; } } $extra = 100.0*$y + $m  190002.5; $rjd = 367.0*$y; $rjd = floor(7.0*($y+floor(($m+9.0)/12.0))/4.0); $rjd += floor(275.0*$m/9.0); $rjd += $d; $rjd += ($uh + ($um + $us/60.0)/60.)/24.0; $rjd += 1721013.5; $rjd = 0.5*$extra/abs($extra); $rjd += 0.5; $rjd = 2415020.5; // JD correction constant for 01/01/1900 $rjd += ($rjd>60) ? 2 : 1; // adjust to inheritet EXCEL/LOTUS bug return $rjd; }
XLubos





Thanks for you script, it works great, but i need to also convert time as well.
how can I do that?
Tariku Giovanni
Web Consultant





the same problem
if you konw the solution, please tell me! thank you.
I like c++





does anyone know if there is a functin to put in a new column to get the abbreviated year and month of a previous column in the same row???
Ex. Row A B C D
3/14/2007 5/16/2007 20073 20075
Instead of using the YEAR MONTH funcion for each row is there a universal function that I could designate to the whole column of C and D???
Thanks!





Hey Victor,
First of all thank you for ur post. Very helpful article.
I am trying to convert your fucntion DMY to Excel Serial Date to PHP and I am newbe in PHP.
function DMYToExcelSerialDate($nDay, $nMonth, $nYear)
{
// Excel/Lotus 123 have a bug with 29021900. 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 calculate with an extra substraction of 2415019.
$nSerialDate = ((1461 * ($nYear + 4800 + ($nMonth  14) / 12) / 4) +
( 367 * ($nMonth  2  12 * ( ($nMonth  14) / 12) ) / 12) 
( 3 * ($nYear + 4900 + ($nMonth  14) / 12) / 100) / 4)+
$nDay  2415019  32075;
if ($nSerialDate <= 60)
{
// Because of the 29021900 bug, any serial date
// under 60 is one off... Compensate.
$nSerialDate;
}
return $nSerialDate;
}
I have put some constants for date values (1st of every month in a year) like from 1/1/2006 (DD,MM,YYYY), 1/2/2006, 1/3/2006.... 1/12/2006(DD/MM/YYYY). But when I am calling this function for convertion its giving the serial numbers.
39083
39114
39144
39175
39205
39235
39266
39296
39327
39357
39388
39418 Which represents in Excel...
1/1/2007
1/2/2007
3/3/2007
3/4/2007
3/5/2007
2/6/2007
3/7/2007
2/8/2007
2/9/2007
2/10/2007
2/11/2007
2/12/2007
This means its going out after februvary. Could u plz help with this. Thanks in advance.
Vijai





When I navigate excel cells, how can I know if the cell value is date?
stanley





Excel recognizes date formats like "10 oct 2005" and stores it internally as a number, the number of days since 111900. This is called a "serial date". You could format a number to show up as a date using the cell's format options...
VictorV





so what if I have 2 cells ..1st one has value 40524 & the 2nd has 12/12/2010. When I get the values both of them are returned as 40524 !! So how do you determine programatically which cell actually was meant for numbers & which was for date because if I have to display the contents of a row (without knowing the format) I want to show 12/12/2010 as 12/12/2010 & not as 40524.
I am using the following code to get values from Excel
Range vrange = worksheet.get_Range("F" + i.ToString(), "V" + i.ToString());
System.Array vmyvalues = (System.Array)vrange.Cells.Value2;
string [] ret = ConvertToStringArray(vmyvalues);
As you can see i am getting values from column F to V & when I iterate through the above string array "ret" I can see the value as of 12/12/2010 as 40524 where as I need it as is shown by excel
Thanks for help.
SunTan





Simple answer: you don't know.
But what is the use to scrape the whole excel worksheet and hoping it is a date?
VictorV





i am trying to convert 01082005 ( august 1st) to serial number and it gives me 31012006 ? Would just like to know why is that? TY





Nvm, Its me who's a jackass. I had to use Fix function for all divisions cuz i use vb instead of c# ( which i did not do). Now it works like a charm.





I believe the last test should be
if (nSerialDate <= 60)
Otherwise, it works fine, thank you very much.





Yes, it seems I've missed this in quality tests
VictorV





You Should add some code like below since the DMYToExcelSerialDate
function does not work when month > 12. The Excel DateSerial function
allows you to calculate serialdates using inputs like (1980, 144, 12)
if (month > 12)
{
adjust = month / 12;
year = year + adjust;
month = month  (adjust * 12);
if (month == 0)
{
month = 12;
year = year  1;
}
}





I stand corrected, mr Anonymous.
However, the .NET DateTime class does not allow illegal year, month or day values, so the algorithm should be fed valid values.
VictorV





This is just what I needed, though in a different language.
I converted this to SAP's ABAP language as we are uploading Excel spreadsheets to SAP and needed to convert from the serial date to the YYYYMMDD format.
As the link that you cite is broken, is there a way to get additional information for all the "magic numbers"?
I believe that your are adjusting the base date back to some other time, which I suspect is the start of the Gregorian calendar. The rest of the calculations have me lost without a reference.
Thanks,
Joe





Hi Joe,
It also took me a while to find the magic numbers. Unfortunately, the source page is indeed gone. I can only suggest to use google now, or look into the other comments with this article: someone ported the code to a much smaller C# version...
VictorV





hi
how do i access the date month and year from the Date command in ms excel ?
i want to compare the month and do an operation
kpmkhaja@hotmail.com





Install the VBA help with Excel and you'll find the answer.
Please constaint your questions to the article.
VictorV





Victor, first off thank you for this helpful article. I used your algorithm in a VB project Im working on but Im getting different results then Excel.
The following code just uses an arbitrary date I chose of 6/28/03. Your Algorithm returns the value of 37678, but Excel returns 37798. Exactly 120 days off. Have I done something wrong in converting the algorithm to VB? I didnt bother putting in the 1900 date checking code as all my dates will be from jan 2003 and up.
Private Sub Command1_Click()
Dim strdate As Date
Dim nDay As Integer
Dim nMonth As Integer
Dim nYear As Integer
Dim serialdate As Long
strdate = "6/26/03"
nDay = DateTime.Day(strdate)
nMonth = DateTime.Month(strdate)
nYear = DateTime.Year(strdate)
serialdate = 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
MsgBox serialdate
End Sub





I know this is a .NET site, but this calculation is exactly what I needed for a PHP project. If anyone's interested, here's the code in PHP. Thanks much for this info!
Nate.
function fmtexcel($days) { // Excel/Lotus 123 have a bug with 29021900. 1900 is not a // leap year, but Excel/Lotus 123 think it is... if ($days <1) return ""; if ($days == 60) { return "2/29/1900"; } else { if ($days < 60) { // Because of the 29021900 bug, any serial date // under 60 is one off... Compensate. $days++; } // Modified Julian to DMY calculation with an addition of 2415019 $l = $days + 68569 + 2415019; $n = floor(( 4 * $l ) / 146097); $l = $l  floor(( 146097 * $n + 3 ) / 4); $i = floor(( 4000 * ( $l + 1 ) ) / 1461001); $l = $l  floor(( 1461 * $i ) / 4) + 31; $j = floor(( 80 * $l ) / 2447); $nDay = $l  floor(( 2447 * $j ) / 80); $l = floor($j / 11); $nMonth = $j + 2  ( 12 * $l ); $nYear = 100 * ( $n  49 ) + $i + $l; return "$nMonth/$nDay/$nYear"; } }





Thanks for the information in your article. I used it to "port" your functions to C# (or .Net if you like). If anybody is interested, here's the code:
public static int ExcelDateNumber(DateTime datetime)
{
int serial = (int) (datetime.Ticks / (10000000L * 24 * 3600))  693593;
if (serial < 60)
{
serial;
}
return serial;
}
public static DateTime ExcelDate(int serialNumber)
{
if (serialNumber < 59)
{
serialNumber++;
}
return new DateTime((serialNumber + 693593) * (10000000L * 24 * 3600));
}
This is all I needed for my purpose, but it would of course be trivial to extend it, for example to allow time information as well.
Kris.





Cool!
About the time: the fraction of the serial date is the fraction of 24 hours: 0.5 is 12:00 (at least that what I think I read).
So it shouldn't be that hard to support time.
VictorV





Here is a modified version that handles time:
public static DateTime ExcelDate(double serialNumber)
{
int datePortion = (int)serialNumber;
if (datePortion < 59)
{
// Because of the 29021900 bug, any serial date
// under 60 is one off... Compensate.
datePortion++;
}
double hourFraction = (serialNumber  datePortion);
return new DateTime((long)((datePortion + 693593 + hourFraction) * (10000000L * 24 * 3600)));
}
public static double ExcelDateNumber(DateTime datetime)
{
double serial = (datetime.Ticks / (10000000L * 24 * 3600))  693593;
/*alternatively you can do:
double serial = ((datetime.Ticks + datetime.TimeOfDay.Tics) / (10000000L * 24 * 3600))  693593;
if thats what you choose to do get rid of the +datetime.TimeOfDay.TotalDays in the return
statement.
*/
if (serial < 60)
{
// Because of the 29021900 bug, any serial date
// under 60 is one off... Compensate.
serial;
}
//This might be .NET 2.0 specific see above comments for alternative
return (serial+datetime.TimeOfDay.TotalDays);
}





Saw this by accident a few days ago, and today I suddenly needed YYMMDD to Excel convertion. Great!





You're welcome. Please vote and contribute to CodeProject!





Was there a strict requirement for your project to use DMY? If not, you might want to consider
using the ISO standard for dates (which is YYYYMMDD).
Aside from being the international standard, this format has a number of advantages (e.g. it is sortable in string representation). For a list of other good reasons to use this format:
http://www.saqqara.demon.co.uk/datefmt.htm
or search google for "iso date standard 8601"
Warren





I'm disappointed that you haven't even read the article and specifically the code at all and comment out of the blue about ISO standards for dates....
The code uses three seperate variables nDay, nMonth and nYear and converts an Excel Serial Date number like 37481 to day 13, month 8, year 2002 and vise vera. NOWHERE is a DMY order enforced. You're welcome to create a ISO Standard date STRING from these numbers using sprintf(szBuff, "%d%d%d", nYear, nMonth, nDay) or any format for that matter...
(DMY is the standard notation order for dates over here in The Netherlands, but everybody all over the world will recognize DMY as being a Day/Month/Year, eg a date.)
VictorV





Victor,
Victor Vogelpoel wrote:
I'm disappointed that you haven't even read the article and specifically the code
I read your entire article, including all of the code in the article. Your article is titled "Excel serial date to DMY and vise versa" (i.e. converting between two representations of date), so I don't think a comment on the international standard for date format qualifies as "out of the blue".
Victor Vogelpoel wrote:
The code uses three seperate variables nDay, nMonth and nYear and converts an Excel Serial Date number like 37481 to day 13, month 8, year 2002 and vise vera. NOWHERE is a DMY order enforced. You're welcome to create a ISO Standard date STRING from these numbers using sprintf(szBuff, "%d%d%d", nYear, nMonth, nDay) or any format for that matter...
I realized that. Changing the format from DMY to YMD doesn't change the code you've presented at all, but that wasn't my point. I added the comment because you mention "DMY" a lot in your article (e.g. the title), and I wanted to point out that there is an international standard for dates, seeing as this website is visited by people from all around the world.
Victor Vogelpoel wrote:
everybody all over the world will recognize DMY as being a Day/Month/Year
There are reasons to justify using all kinds of different formats (DMY, YMD, MDY, etc), but as long as multiple formats are used around the world, there will be confusion. There is already an international standard, so why not use it?
If you want to use DMY as your format, I'm not going to be able to convince you to do otherwise. I just wanted to point out that there is an international standard for dates, in case some other reader of your article might be interested.
Warren





Now I see what the confusion is!
It seems changing the title to "Excel serial date to Day, Month, Year and vise versa" would end this discussion and clarify the true intent of the article, wouldn't it?
VictorV







General News Suggestion Question Bug Answer Joke Praise Rant Admin Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

