#22 Date off by one day

[Anonymous]

Dates that are formated using Excel type *3/14/2001
(the first date format option) are read as one day
earlier. For example, if the workbook has 10/5/2004
then the $data array has 10/4/2004 in 'cells'.

Excel format type 3/14/2001 (the second to last Excel
date format option) and perhaps all other standard
Excel date formats return a number which is fine since
I can easily convert that to a mysql style date string
using a function from my date library.

A custom format on date such as mm/dd/yyyy returns
strange strings like 04/14/01010101.

I changed reader.php so that is sets $string =
$numValue instead of date ($this->curformat, $utcValue)
in createDate to get around the problem but you may
want to look into it.

[Nobody/Anonymous]

Logged In: NO

the suggested correction did not work, for me. The solution
that I implemented and that did work for me was to change
the line

$utcDays = $numValue - ($this->nineteenFour ?
Spreadsheet_Excel_Reader_utcOffsetDays1904 :
Spreadsheet_Excel_Reader_utcOffsetDays);

to

$utcDays = $numValue - ($this->nineteenFour ?
Spreadsheet_Excel_Reader_utcOffsetDays1904 :
Spreadsheet_Excel_Reader_utcOffsetDays) + 1 ;

in the function createDate

[Nobody/Anonymous]

Logged In: NO

the above solution almost worked for me. it doesn't seem to
work on leap years, though (it's off by one). so i added a
special case for that (this is crappy, i know):

$utcDays = $numValue - ($this->nineteenFour ?
Spreadsheet_Excel_Reader_utcOffsetDays1904 :
Spreadsheet_Excel_Reader_utcOffsetDays) + 1;

$utcValue = round($utcDays * Spreadsheet_Excel_Reader_msInADay);

$year = date("Y", $utcValue);

if($year % 4 == 0) $utcValue = $utcValue - 24*60*60; // go
back a day

[Nobody/Anonymous]

Logged In: NO

I think the problem is that
$utcDays*Spreadsheet_Excel_Reader_msInADay is not a good
conversion to a unix timestamp, because it doesn't account
for leap years.

Here is my solution to fix the original createDate():

function createDate($numValue){
if ($numValue > 1){
if ($this->nineteenFour) {
$jd0 = gregoriantojd(1, 1, 1904);
} else {
$jd0 = gregoriantojd(1, 1, 1900);
}
$jdDay = $numValue + $jd0;
$utcValue = jdtounix($jdDay);
$string = date ($this->curformat, $utcValue);
$raw = $utcValue;
} else {
... this is all the same as before

[omaeryx]

Logged In: YES
user_id=1420026

This is a crappy workaround for the wrong parse of
mm/dd/yyyy dates. Sucks, but it worked for me. :D

function createDate($numValue){
if ($numValue > 1){
$utcDays = $numValue - ($this->nineteenFour ?
Spreadsheet_Excel_Reader_utcOffsetDays1904 :
Spreadsheet_Excel_Reader_utcOffsetDays);
$utcValue = round($utcDays *
Spreadsheet_Excel_Reader_msInADay);

if ($this->curformat == "i/dd/yyyy"){
$this->curformat = "m/d/Y";
}

$string = date ($this->curformat, $utcValue);
$raw = $utcValue;
}else{
$raw = $numValue;
$hours = floor($numValue * 24);
$mins = floor($numValue * 24 * 60) - $hours * 60;
$secs = floor($numValue *
Spreadsheet_Excel_Reader_msInADay) - $hours * 60 * 60 -
$mins * 60;
$string = date ($this->curformat, mktime($hours,
$mins, $secs));
}
return array($string, $raw);
}

[Nobody/Anonymous]

Logged In: NO

I ran into this same problem.

Date '5/1/2006' in Excel, was coming out one day earlier:
4/29/2006

Taking a further look at the generated unix_timestamp:
1146355200, a mysql "from_unixtime( 1146355200 )" revealed
it to be: 2006-04-29 18:00:00 ... in this case, it's about 6
hours or so off from being true 5/1/06.

I wonder if local time zone could be a culprit here? Brief
review of the class code wasn't too revealing...

I fear that I simply added +1 as well in hopes my dates are
covered. Appeared to work fine for both leap years AND
non-leap...

1/1/200 = 946771200 = "2000-01-01 17:00:00"

Odd that here the 18:00:00 would drop to 17:00:00
...let's take a look into the future:

1/1/2038 still seems to work fine, too

Close cut-off point at 1/1/2039 (and higher)..causes date
func to fail: 2177539200

[Nobody/Anonymous]

Logged In: NO

2006-02-15 16:54 was on the right track, but used the wrong date for the windows excell epoch.

it should be:

$jd0 = gregoriantojd(12, 30, 1899);

instead of

$jd0 = gregoriantojd(1, 1, 1900);

[Krishnaprasad M G]

This is working fine for me in a Windows server running apache( it will always return date in dd/mm/yyyy format)

change the createDate function to

function createDate($numValue)
{
$this->curformat = 'd/m/Y';
if ($numValue > 1) {
$utcDays = $numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS)-1;
$utcValue = round(($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY);
$string = date ($this->curformat, $utcValue);
$raw = $utcValue;
} else {
$raw = $numValue;
$hours = floor($numValue * 24);
$mins = floor($numValue * 24 * 60) - $hours * 60;
$secs = floor($numValue * SPREADSHEET_EXCEL_READER_MSINADAY) - $hours * 60 * 60 - $mins * 60;
$string = date ($this->curformat, mktime($hours, $mins, $secs));
}

return array($string, $raw);
}

[Nobody/Anonymous]

Thank you 2009-05-06 12:24:20 UTC, worked great !