Dates before 14 Dec 1901

Topics: Developer Forum
Jun 6, 2010 at 9:24 PM

I am using PHPExcel to spot updated records in an Excel spreadsheet and update a database.  Having been programming in Ruby recently, it was a pleasure to find such excellent documentation here.  All is working very well except this oddity.

If I extract a date of birth out of the Excel with a cell value between 1 and 714, I would expect PHPExcel_Shared_Date::ExcelToPHP to convert it to a date before 14 Dec 1901.  But for some reason, these numbers convert to dates between 7 Feb 2036 and 18 Jan 2038.  Is this a known problem or am I doing something stupid?

Test program:

<?php

// Error reporting
error_reporting(E_ALL);

// PHPExcel_IOFactory
require_once '../Classes/PHPExcel/IOFactory.php';

for ( $i=0; $i<10; $i++ ) {

  echo "date $i = " . date( "j M Y", PHPExcel_Shared_Date::ExcelToPHP( $i ) ) . "\r\n";

}
for ( $i=710; $i<719; $i++ ) {

  echo "date $i = " . date( "j M Y", PHPExcel_Shared_Date::ExcelToPHP( $i ) ) . "\r\n";

}

?>

Output:

date 0 = 6 Jun 2010
date 1 = 7 Feb 2036
date 2 = 8 Feb 2036
date 3 = 9 Feb 2036
date 4 = 10 Feb 2036
date 5 = 11 Feb 2036
date 6 = 12 Feb 2036
date 7 = 13 Feb 2036
date 8 = 14 Feb 2036
date 9 = 15 Feb 2036
date 710 = 15 Jan 2038
date 711 = 16 Jan 2038
date 712 = 17 Jan 2038
date 713 = 18 Jan 2038
date 714 = 14 Dec 1901
date 715 = 15 Dec 1901
date 716 = 16 Dec 1901
date 717 = 17 Dec 1901
date 718 = 18 Dec 1901

Note that dates are correct from 714 onwards.

(Has my loop given away that I first learn to program in Fortran 77? :-) )

Coordinator
Jun 6, 2010 at 10:43 PM

The reason for this is that the PHP date/timestamp is actually an integer with a range between -2147483648 and 2147483647 on a 32-bit server. With the PHP base date of January 1st 1970 00:00:00 UTC, and the date/timestamp being measured as the number of seconds from that point, this gives a date range of between Friday, 13th December 1901 20:45:54 UTC to Tuesday, 19th January 2038 03:14:07 UTC.
Prior to PHP 5.1.0 on Windows servers, negative values weren't used for date/timestamps, so dates prior to 1st January 1970 weren't possible

The Excel date is a 32-bit positive floating point value, with a (default) base date of January 1, 1900 00:00:00 (the Windows 1900 calendar) = 1.0, and measures the number of days since that base date, with times being represented by the fractional part... .25 corresponding to 6am, .5 to midday and .75 to 6pm.

PHPExcel holds the date internally as the Excel value, and the PHPExcel_Shared_Date::ExcelToPHP() method allows you to convert that value to a PHP date/timestamp. However, it is subject to the PHP date range limitations, as you have discovered.

 

There are two options that allow you to work with the full Excel date range.

Option 1 is to work on a 64-bit PHP server, with the PHP date/timestamp as a 64-bit integer. This gives a much wider date range using the standard PHP date/timestamp.

Option 2 is to use PHP's date/time objects, which inherently support a much wider date range, even on 32-bit PHP. PHPExcel uses these internally to handle date conversions. The PHPExcel_Shared_Date::ExcelToPHPObject() will convert an Excel date/time to a PHP date/time object.

 

A couple of other minor quirks that you may notice when playing with dates: 29th February 1900 is a valid date in Excel, even though 1900 wasn't a leap year; and Excel workbooks may also have a different base date, the Mac 1904 calendar with a base date of 2nd Jan 1904 = 1.0.

 

 The Fortran 77 background doesn't particularly show... if you'd tried to write it as a do loop, then I might have suspected :) but it's nice to find somebody else that still knows Fortran.

Jun 7, 2010 at 10:08 PM

Thanks for your help  Mark.  

I have programmed a quick-and-dirty workaround by adding (365+365+365+366) to the number to take it four years on from the problem area,, formatting it and then taking four years back off the formatted number.  I'm sure there are ,ore elegant solutions but this works so I will move on.

Feb 25, 2014 at 8:01 AM
This is a input file format. How could I get these values, Could you help me ?

Prof: 1
Term: 1
Subject Code: 1
CP: wrtnMarks
Year : 2014

Id Marks

1 34
41 44
42 44



I will insert id and marks in a table depending on top data as condition.