I am using PHPExcel extension for read/write excel files. Now I have a problem with reading the date format from excel file. In the excel file, for the date column, the data is "12/31/2012". ie "mm/dd/yyyy".
This is plain simple. Day/Time information are stored in Excel as plain numeric values. The only thing what makes them dates/times is cell formatting.
So, what’s happening in the background?
PHPExcel reads a cell and finds a numeric value in cell and data/time formatting information attached to it. Ah so! - PHPExcel thinks - this number is a date/time value in fact!
So, what PHPExcel is doing at this moment?
$cellValue = {the numeric value read from Excel cell}
$dateTime = new DateTime($cellValue);
Only this! So, what formatting will be applied to this new DateTime object?
The answer is - default formatting! And in case of PHP that is dependent on default_time_zone value of the WWW server!
So, either you have to ensure server time zone information is appropriate to your needs or you have to format your output manually using appropriate DateTime object formatting.
Oh yes - sure - here you’ve defined custom formatting in another way, but that’s the case - date/time formatting is not retained from Excel but has to be re-declared in PHP.