Php – Opening a CSV file in excel changes the formats

csvdatetime-formatexcelphp

I have a CSV File which has a DateTime Column that i used php to alter in order for it to be in this format, 02/18/2013 03:53:48 PM

But when this file is opened up in excel all the formats are changed to this format,
02/18/2013 15:53

Now this would not be a problem for me to look at or deal with but i want the end user to not have to look at the time in a 24 hour format in excel. How do i make excel not change my format once the csv is opened AND allow the end user to sort this DateTime column without any issues. I am aware i can change the format of the date by formatting the cells once i open the file but i don't want the end user to even have to take this step.

I'd also like to point out that when i select a cell the formula bar will show my DateTime column in the format i want which baffles me.

Best Solution

CSV Files don't have a format, they just contain data.

MS Excel will examine that data, and try to set an appropriate format for it. It right-justifies numeric values, for example. It can recognise a string containing a human-readable date/time value, and converts it to its own internal timestammp value (represented as a floating point number) and then applies an appropriate date/time mask to that cell so that it's styles as a date or time, but not necessarily using the string representation that you had in your CSV file.

Solution#1, prefix your formatted date string with an = symbol and wrap it in quotes so it's treated as a string.

Solution#2, write an actual Excel file where you can control the formatting of cells.