PHP reading dates from excelsheets

Question

I am trying to read dates from .xlsx file. My code looks like this

$reader = 
PHPExcel_IOFactory::CreateReaderforFile($name);
$rawData = $reader->load($name);
$sheet = $rawData->getSheet(0);
$lastRow = $rawData->getHighestRow();
$excel_arr = $sheet.toArray(null, true, true, true);

Assume I have date as 01/08/1929 in A2 cell of my excel Am reading it as $excel_arr[2][A] , output am getting is 01-08-29, I want year as four digit, because year in 01-08-29 is getting interpreted as 2029 instead of 1929.

If I have date as 01/08/2029 then also it will be shown as 01-08-29.

Could someone please help

You can check this post: https://stackoverflow.com/questions/33581012/create-date-object-in-php-for-dates-before-1970-in-certain-format — Moshe Fortgang, Jul 30 '19 at 13:03
PHP dates "start" at 1970. You will need to adjust the code that is processing the spreadsheet to adjust years earlier than that to meet your criteria. That said if you process your spreadsheet row by row you should be able to get the value of the cell which will include the year. — Dave, Jul 30 '19 at 13:13
https://stackoverflow.com/questions/41239008/how-to-convert-year-format-yy-into-yyyy-in-php - You can convert a 2 digit year into a 4 digit year. — unixmiah, Jul 30 '19 at 17:35

score 1 · Answer 1 · answered Jul 30 '19 at 18:03

You can do something like this:

<?php                                                                                                                                                                                                 
$date = "01-08-29";                                                                                                                                                                                   
$dates = DateTime::createFromFormat('m-d-y', $date);                                                                                                                                                  
$arr = $dates->format('m-d-Y');                                                                                                                                                                       
echo $str2 = date('m-d-Y', strtotime('-36500 days', strtotime($arr))); //this will result in 08-26-1929                                                                                              
?>

Thanks for the technique, but what if the date in excel cell is 01/08/2029, as per your code it also gives 1929 which is not correct. — Sri, Jul 30 '19 at 19:34

artuc · Answer 2 · 2019-07-31T10:52:26.240

Attached code will return PHP Date object and you are good to go

<?php
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load("test.xlsx");
$sheet = $spreadsheet->getSheet(0);
$cell = $sheet->getCell('B3');
$dateVal = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($cell->getValue()); 

?>

Returns:

 DateTime Object
 (
  [date] => 1929-08-02 00:00:00.000000
  [timezone_type] => 3
  [timezone] => UTC
 )

PHP reading dates from excelsheets

2 Answers2