
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







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.

