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: stackoverflow.com/questions/33581012/… — Moshe Fortgang
– Moshe Fortgang, Commented Jul 30, 2019 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
– Dave, Commented Jul 30, 2019 at 13:13
stackoverflow.com/questions/41239008/… - You can convert a 2 digit year into a 4 digit year. — unixmiah
– unixmiah, Commented Jul 30, 2019 at 17:35

unixmiah · Accepted Answer · 2019-07-30 18:03:25Z

1

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                                                                                              
?>

answered Jul 30, 2019 at 18:03

unixmiah

3,1691 gold badge16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sri Over a year ago

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.

artuc · Accepted Answer · 2019-07-31 10:52:26Z

0

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
 )

edited Jul 31, 2019 at 10:52

answered Jul 30, 2019 at 20:14

artuc

91311 silver badges20 bronze badges

Collectives™ on Stack Overflow

PHP reading dates from excelsheets

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related