PHPExcel not reading user defined date formats

Question

Trying to parse excel sheets but running into issues with user-defined date formats. The date format does not get added to the formattedData. I have no way of knowing what cell is going to contain a date and the dates come in all types of formats.

Example: M/D/YYYY - 11/17/2015

After parsing the sheet I get the floating point date with no format. I did not echo out the format from PHPExcel on this one.

In another case I echoed out the get format data and it was General for the column column that contained the date, but in excel it had a valid date format albeit user defined of D-MMM-YY - 15-Jul-14.

Testing for dates in these scenarios did not work either using,

PHPExcel_Shared_Date::isDateTime($cell);

So to the question, how do I get the date format or test if it is a date when the format is user defined?

Are you reading this file with readDataOnly set to true? If so, then you're explicitly telling PHPExcel not to read the formatting at all, but simply to read only the raw data — Mark Baker
What format is this file that you're loading? Because if it's a valid spreadsheet file, then you've identified yet another way of breaking PHPExcel that I can't even begin to guess at.... format should always be read unless its explicitly suppressed.... is it possible for you to send me a copy of this file so that I can try and figure out what's wrong? — Mark Baker
And this has nothing to do with user-defined formats..... there's no difference between user-defined and Excel-defined as far as reading the file is concerned — Mark Baker

Joseph Batson Joseph Batson · Accepted Answer · 2016-01-06T17:04:55

Same issue as PHPExcel number formats for dates

It is using built-in number format id's for custom date formats.

PHPExcel not reading user defined date formats

1 Answers