CODEWITHSUNDEEP
phpexcelcsvdatetime-format
Guy code man
Guy code man

Reputation: 85

Opening a CSV file in excel changes my formats

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.

Upvotes: 4

Views: 9946

Answers (3)

K_B
K_B

Reputation: 3678

They way the time will show is a setting in Windows or (if users set it different) in Office. If you want something that looks like a date/time NOT formatted by the Excel application on opening then you should not make it a date/time in the first place.

Excel will see anything that is rpeceded by a single straight quote as text so you could store your date/time values as "'02/18/2013 03:53:48 PM"

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212412

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.

Upvotes: 5

fullybaked
fullybaked

Reputation: 4127

If you want to control the format of a final Excel file you will need to output your data into that format and modify it accordingly using a PHP Excel library. I can't tell you exactly how to do this as I don't know which library you may decide to use.

I've used http://phpexcel.codeplex.com/ to some degree of success

If you just output a CSV then Excel will open it with the users default settings for formatting which may be different to what you want.

Upvotes: 1

Related Questions