The reader sometimes adds one - Day to a Date. I edited the function "createDate($numValue)" :
cange: $utcValue = round(($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY);
to:
$utcValue = round(($utcDays) * SPREADSHEET_EXCEL_READER_MSINADAY);
This isn't a bug with the reader, but rather it's a bug with Microsoft Excel. Excel stores dates by using a 1900 epoch--meaning the number of days since jan 1st. 1900. But when they did this, some Microsoft genius thought that 1900 was a leap year and so there is an extra day there. I've actually written a function to convert the epoch into a readable date. You can reformat the date by changing the return value.
Nitin
nitx.com
function xls1900EpochToDate($days) {
// 1900 leap year bug in excel
if ($days < 1) {
return "";
}
if ($days == 60) {
return "1900-02-29";
}
// because of 1900 leap year bug, all dates after are 1 day off
else {
if ($days < 60) {
$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 $nYear."-".$nMonth."-".$nDay;
}
}
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
new source-file
Hi,
Can you help me to use the php file correctly. I am not getting a human readable string. See my code segment below.
Thanks,
Bk.
Code:
$myDate = $data->sheets[$sheet]['cells'][$row][$column];
print $myDate;
Output:
39962.56
Expected:
29/05/2009
This isn't a bug with the reader, but rather it's a bug with Microsoft Excel. Excel stores dates by using a 1900 epoch--meaning the number of days since jan 1st. 1900. But when they did this, some Microsoft genius thought that 1900 was a leap year and so there is an extra day there. I've actually written a function to convert the epoch into a readable date. You can reformat the date by changing the return value.
Nitin
nitx.com
function xls1900EpochToDate($days) {
// 1900 leap year bug in excel
if ($days < 1) {
return "";
}
if ($days == 60) {
return "1900-02-29";
}
// because of 1900 leap year bug, all dates after are 1 day off
else {
if ($days < 60) {
$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 $nYear."-".$nMonth."-".$nDay;
}
}
please, fix this "bug" or say how I have to change the code to fix it, thank you