UK formatted date appearing as US format

Topics: Developer Forum
May 13, 2013 at 4:31 PM
Edited May 14, 2013 at 9:23 AM
Hi folks,
I'm reading an Excel 2007 .xlsx file (created in the United Kingdom locale) containing a few dates which have been automatically formatted in Excel to UK format, so they appear as (eg.) 01/02/2013.

When I use PHPExcel to read the file using toArray() with setReadDataOnly(false), it shows me dates in US format (eg: 02-01-2013).

I need these to be in the format the user is expecting them, in this case UK, but possibly anything else. PHPExcel is running on the same machine the file was created, if that matters.

Why are these formatted as US dates and how can I change them? My system does not know which cells are dates and processes large files so I checking each cell for specific formatting is not something I want to do.

  • Edit:
    The dates use locale dependant formatting (marked with an asterisk * in excel) so I guess that's why its happening, but I still need it to show up in the right format because I need to show the data to the user when they upload a file. Since I don't know which fields contain dates, I can't force formatting on the values.
Is there a way to find out for certain if a cell contains a date?
May 14, 2013 at 3:34 PM
PHPExcel_Shared_Date::isDateTime(PHPExcel_Cell $pCell)
Use getFormattedValue or, if this not work, toFormattedString with a custom format.
May 15, 2013 at 2:21 PM
Thanks for your reply. I see how that works.
Unfortunately, this is a per-cell solution and I was hoping not to have to check every cell. I might have to re-think this completely.