Jan 21 2021 09:37 AM
Hi everyone. I have this export where some dates are formated correctly and some are not in the same column. I have tried the obvious: date format, text to columns, =DATEVALUE, and pretty much all the solutions I have found online. Windows is also set to the correct date format.
I am about to go bananas due to this. Does any have a clue how to solve this?
Thank you.
Jan 21 2021 10:48 AM
Is it possible for you to post a copy of the spreadsheet itself? That would help us help you better than a description, especially given all the things you've already tried.
Jan 21 2021 11:29 AM
Thanks. hope you can help.
Here it is, I have removed some data that is not relevant. As you can see I also have problems with ',' and '.' in regular numbers. Easier to fix with find and replace. Still don't get it why some are formated correctly and some are not.
Jan 21 2021 11:38 AM
date format won't work, Text to Columns shall if on third step of the wizard you select Date and MDY
Jan 21 2021 11:41 AM
That is fixed by Text to Columns as above
Jan 21 2021 11:54 AM - edited Jan 21 2021 12:00 PM
What? That is absolutely crazy. Would have never guessed that the solution is choosing a different format than the one you want. Thank you so much. You have saved my sanity!
Jan 21 2021 12:46 PM
@Felicia400 , you are welcome.
Dates in Excel are actually numbers which are presented in human friendly date format. If you have text, which is looks like date, applying any format to it won't convert such text to number. That's what Text to Columns does. You say to it on third step - look, I have the text which presents the date in MDY format and which shall be considered as date. Based on such instruction Excel converts such texts in the columns into dates (aka numbers which will be shown in your default date format) and ignores all other values.
Please be careful with default transforming. If, for example, you open csv file with 02/03/2021 value (actually text) Excel could convert into 2nd of Mar or 3rd of Feb depends on your locale default format. You shall to check such default transformations to be sure Excel correctly recognized the dates.