Menu
We're exporting our analytics reports in various formats, among them CSV. For some clients this CSV finds it's way into Excel.
Inside the CSV file one of the columns is a Date, for example
"Start Date","Name"
"07-04-2010", "Maxim"
Excel has trouble parsing this date format, obviously depending on the Locale of the user. Is "07" is the day or the month...
Could you recommend some textual format for a Date field that excel will not have trouble parsing? I'm aiming at the most fail safe option possible. I would settle for some escape sequence that will cause excel to avoid parsing the text in the column altogether.
Thanks for helping, Maxim.
766
If the format is changed to d-mmm-yyyy, the date becomes 1-Jan-2021. We can change the date format in Excel either from the “Number Format” of the “Home” tab or the “Format Cells” option of the context menu. In Excel for Windows, 1900 is the default date system.
The most likely problem is that the dates are text not numbers. Try referencing one of the date cells with the formula =ISTEXT(A10) where A10 is one of the problem cells. If the result of the formula is TRUE you know you have the wrong data type, text not numbers.
First, pick the cells that contain dates, then right-click and select Format Cells. Select Custom in the Number Tab, then type 'dd-mmm-yyyy' in the Type text box, then click okay.
You have two options. Go with the month as a string and the year as 4 digits, or use ISO formatting: yyyy-mm-dd.
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
