How to convert a serial date of Excel to Mysql Date time?

pullaratt

Hi All,

Is anyone there to help me? 🙂

When i am tranfering the excel spread sheet data into Mysql through PHP by the method of excel COM object, I found that the date type of excel and mysql are not compatible. Excel using serial date (ie, how many days after 1900 Jan 0) and i can't convert this into mysql datetime. :eek:

I want to convert the serial date in excel to Mysql Date Time, Is this possible through PHP and how? 😕

Thanks in advance

Bye...

rincewind456

From the Manual http://uk.php.net/manual/en/ref.datetime.php

<?
function xl2timestamp($xl_date)
{
$timestamp = ($xl - 25569) * 86400;
return $timestamp;
}
?>

Weedpacket

Well, I'm going to guess you're using Windows, whose date/time functions can't tolerate anything before Jan 1, 1970.

$excel_timestamp = whatever-25567; // 1970-01-01 is day 25567.
$php_timestamp = mktime(0,0,0,1,$excel_timestamp,1970); // No, really - this works!
$mysql_timestamp = date('Y-m-d', $php_timestamp); // Or whatever the format is.

If you're running on a Unix system subtracting seventy years isn't necessary:
to

$excel_timestamp = whatever;
$php_timestamp = mktime(0,0,0,1,$excel_timestamp,1900);
$mysql_timestamp = date('Y-m-d', $php_timestamp);

(Incidentally, I'm assuming that Excel uses '1' to denote 1900-01-01.)

Roger_Ramjet

Good little function that xl2timestamp, very usefull.

However, I find so many other problem with data types from excel, like integers decimals and currency being stored as doubles, that I ALWAYS export the data from excel into a delimited text file before importing it into anything else. That way you can get date fields and the like as text strings in the same format as you have chosen for their display in excel. If you are exporting the same spreadsheet layout frequently, you can save an export specification to automate the process of controlling layout and content.