Skip to content

Instantly share code, notes, and snippets.

@benjibee
Last active October 12, 2021 15:10
Show Gist options
  • Save benjibee/3e6189e6114bc591128f1d8f5f7c9edd to your computer and use it in GitHub Desktop.
Save benjibee/3e6189e6114bc591128f1d8f5f7c9edd to your computer and use it in GitHub Desktop.
Convert Excel date / time to PHP date
<?php
/**
* Convert date and time from Excel into a PHP DateTime object
*
* Based on similar function from PHPExcel library.
*
* @see https://stackoverflow.com/a/11172688/401980
* @see http://www.cpearson.com/excel/datetime.htm
*/
function excel_to_date($date_value = 0, $format = null)
{
/**
* Number of days between the beginning of serial date-time (1900-Jan-0)
* used by Excel and the beginning of UNIX Epoch time (1970-Jan-1).
*/
$days_since_1900 = 25569;
if ($date_value < 60) {
--$days_since_1900;
}
/**
* Values greater than 1 contain both a date and time while values lesser
* than 1 contain only a fraction of a 24-hour day, and thus only time.
*/
if ($date_value >= 1) {
$utc_days = $date_value - $days_since_1900;
$timestamp = round($utc_days * 86400);
if (($timestamp <= PHP_INT_MAX) && ($timestamp >= -PHP_INT_MAX)) {
$timestamp = (integer) $timestamp;
}
} else {
$hours = round($date_value * 24);
$mins = round($date_value * 1440) - round($hours * 60);
$secs = round($date_value * 86400) - round($hours * 3600) - round($mins * 60);
$timestamp = (integer) gmmktime($hours, $mins, $secs);
}
return $format ? date($format, $timestamp) : $timestamp;
}
echo excel_to_date(44641, 'd.m.Y H:i:s');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment