I am uploading an Excel file through PHP Excel. The column C has dates in format mm/dd/yyy.
When I echo the date for example '10/3/2016' it reads it as 42646. The format set for the column is short Date.
How can I read it as the normal date instead of 42646.
here is the model
function fi_upload($file_name){
$file = './uploads/'.$file_name;
$this->load->library('excel');
$objPHPExcel = PHPExcel_IOFactory::load($file);
$cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();
foreach ($cell_collection as $cell) {
$column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
$row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
$data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();
if ($row == 1) {
$header[$row][$column] = $data_value;
} else {
$arr_data[$row][$column] = $data_value;
}
}
$data['header'] = $header;
$data['values'] = $arr_data;
$datecell = $objPHPExcel->getActiveSheet()->getCell('P2');
if(PHPExcel_Shared_Date::isDateTime($datecell)) {
$InvDate = date($format="Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($datecell));
}
echo $InvDate;
exit();
foreach ($arr_data as $q){
echo $q['C']; // column of date, date is echoed as 42646
exit(); //here i exit to display the date
}
I have to later insert it in the database as well in the yyyy/mm/dd format (which I can do if it is read properly)
Thanks.
UPDATE:
So apparently it was an Excel file issue, not php. Converting the cell values through TEXT() was the solution as suggested by @Hallur.
UPDATE 2.0: I am trying to do it via PHPEXCEL method but I get the following two issues.
42646 is an MS Excel serialized timestamp value, the number of days since 1st January 1900 (or 1st January 1904, depending on whether the spreadsheet is using the Windows or Mac calendar)
PHPExcel provides a variety of functions to convert values between MS Excel serialized timestamps and Unix timestamps or PHP DateTime objects (or vice versa), all of which can be found in the PHPExcel_Shared_Date class, e.g.
ExcelToPHP() to convert an MS Excel serialized timestamp to a unix timestampExcelToPHPObject() to convert an MS Excel serialized timestamp to a PHP DateTime objectAlternatively, using the getFormattedValue() method instead of getValue() will return a formatted date/time string value instead of the serialized tiemstamp
The answer to this question is here:
Excel weird behaviour with dates => text
The number 42646, is the amount of days since january 1st year 1900.
To convert the excel base date to unix time ( time() ), you must calculate the difference between unix start to excel base time start. (unix start: d-m-Y H:i:s -> 01-01-1970 01:00:00) (excel start: d-m-Y -> 1-1-1900)
First to get the difference, which is approximately 70 years and 1 hour (+ 1 extra day because excel date 1-1-1900 = 1, not 0.)
70 years, 1 day, 1 hour = 70 years + 25 hours. (to make up for leap years, we have to say 365.25 instead of 365 days in a year) (70*365.25*24*60*60)+(25*60*60)
Now, we need to convert the excel date value to seconds as well: 42646*24*60*60 (~ 116 years)
We need to subtract them from each other to get the unix time, and since the excel date is higher than the 70 years, we put that first.
to echo out the day:
echo date("d-m-Y", (42646*24*60*60) - ((70*365.25*24*60*60)+(25*60*60)));
EDIT: I just realized that extra hour is because of my timezone.. CET, not UTC.
$field1= $objWorksheet->getCellByColumnAndRow(0,$i)->getFormattedValue(); //Excel Column 3
$date = PHPExcel_Shared_Date::ExcelToPHP($field1); //unix
echo $field1= gmdate("Y-m-d", $date); //date