I read the date from excel using php and convert to sql format using following code
$date = date('Y-m-d', strtotime($cell));
but after convertion the date is not correct. how to solve this problem?
The value store in PHPExcel is an Excel serialized datetime value (a float, representing the number of days since 1/1/1900 or 1/1/9004 depending on whether Windows or Mac Calendar was used). You need to convert this to a Unix timestamp or a PHP DateTime object to use PHP date functions against it for setting the format
Taken directly from the PHPExcel Date handling code:
function ExcelToPHP($dateValue = 0, $ExcelBaseDate = 1900) {
if ($ExcelBaseDate == 1900) {
$myExcelBaseDate = 25569;
// Adjust for the spurious 29-Feb-1900 (Day 60)
if ($dateValue < 60) {
--$myExcelBaseDate;
}
} else {
$myExcelBaseDate = 24107;
}
// Perform conversion
if ($dateValue >= 1) {
$utcDays = $dateValue - $myExcelBaseDate;
$returnValue = round($utcDays * 86400);
if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
$returnValue = (integer) $returnValue;
}
} else {
$hours = round($dateValue * 24);
$mins = round($dateValue * 1440) - round($hours * 60);
$secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
$returnValue = (integer) gmmktime($hours, $mins, $secs);
}
// Return
return $returnValue;
} // function ExcelToPHP()
Set $ExcelBaseDate to 1900 or 1904 as necessary to indicate the Excel base calendar that you're using: Windows 1900 or Mac 1904
and if you want a PHP DateTime object instead:
function ExcelToPHPObject($dateValue = 0) {
$dateTime = ExcelToPHP($dateValue);
$days = floor($dateTime / 86400);
$time = round((($dateTime / 86400) - $days) * 86400);
$hours = round($time / 3600);
$minutes = round($time / 60) - ($hours * 60);
$seconds = round($time) - ($hours * 3600) - ($minutes * 60);
$dateObj = date_create('1-Jan-1970+'.$days.' days');
$dateObj->setTime($hours,$minutes,$seconds);
return $dateObj;
} // function ExcelToPHPObject()
$dateValue argument is your Excel serialized timestamp (34394); the $ExcelBaseDate identifies which calendar MS Excel is using, which can be either the Windows 1900 calendar (base date is 1st January 1900) or the Mac 1904 calendar (base date is 1st January 1904).... in your case, if 34394 is the 1st March 1994, your using the Windows 1900 calendar which is the more common; so you don't need to pass any $ExcelBaseDate argument as it will default to the Windows 1900 calendartry
$date = str_replace('/', '-', $cell);
$start_date = date('Y-m-d', strtotime($date));
$cell? in terms ofdd-mm-yyyetc...$cellneeded