4

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.

Guy code man
  • 85
  • 1
  • 3
  • 7

3 Answers3

5

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.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 1
    Well if i change the date to a string i cant sort from newest to oldest correctly.(which may be the reason the end user wants to use excel). – Guy code man Apr 30 '13 at 12:49
  • That's the drawback, which leaves solution #2 as your option - there are oyjer benefits to that, such as being able to use formatting (e.g. bold header row, frozen panes, conditional formatting, etc) – Mark Baker Apr 30 '13 at 12:54
  • Well Option 2 may be the only solution. Thank you for your input. – Guy code man Apr 30 '13 at 13:03
1

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.

fullybaked
  • 4,117
  • 1
  • 24
  • 37
0

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"

K_B
  • 3,668
  • 1
  • 19
  • 29
  • Yes i know about this and ty for your answer but i dont want to perform any actions in excel just wanted it to stay in the format i had it in. Apparently this is not possible. – Guy code man Apr 30 '13 at 12:51
  • Sorry for that, I have changed my answer in line with what you are actually looking for – K_B Apr 30 '13 at 12:56