Dates won't format

%3CLINGO-SUB%20id%3D%22lingo-sub-2083394%22%20slang%3D%22en-US%22%3EDates%20won't%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083394%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone.%20I%20have%20this%20export%20where%20some%20dates%20are%20formated%20correctly%20and%20some%20are%20not%20in%20the%20same%20column.%20I%20have%20tried%20the%20obvious%3A%20date%20format%2C%20text%20to%20columns%2C%20%3DDATEVALUE%2C%20and%20pretty%20much%20all%20the%20solutions%20I%20have%20found%20online.%20Windows%20is%20also%20set%20to%20the%20correct%20date%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20about%20to%20go%20bananas%20due%20to%20this.%20Does%20any%20have%20a%20clue%20how%20to%20solve%20this%3F%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Felicia400_0-1611250088276.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F248108iA26806DF32AD867C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Felicia400_0-1611250088276.png%22%20alt%3D%22Felicia400_0-1611250088276.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2083394%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2083826%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20won't%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083826%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937867%22%20target%3D%22_blank%22%3E%40Felicia400%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20for%20you%20to%20post%20a%20copy%20of%20the%20spreadsheet%20itself%3F%20That%20would%20help%20us%20help%20you%20better%20than%20a%20description%2C%20especially%20given%20all%20the%20things%20you've%20already%20tried.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2083961%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20won't%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20hope%20you%20can%20help.%3C%2FP%3E%3CP%3EHere%20it%20is%2C%20I%20have%20removed%20some%20data%20that%20is%20not%20relevant.%20As%20you%20can%20see%20I%20also%20have%20problems%20with%20'%2C'%20and%20'.'%20in%20regular%20numbers.%20Easier%20to%20fix%20with%20find%20and%20replace.%20Still%20don't%20get%20it%20why%20some%20are%20formated%20correctly%20and%20some%20are%20not.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2083988%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20won't%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937867%22%20target%3D%22_blank%22%3E%40Felicia400%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Edate%20format%20won't%20work%2C%20Text%20to%20Columns%20shall%20if%20on%20third%20step%20of%20the%20wizard%20you%20select%20Date%20and%20MDY%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

Felicia400_0-1611250088276.png

 

 

6 Replies

@Felicia400 

 

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.

@mathetes 

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.

@Felicia400 

date format won't work, Text to Columns shall if on third step of the wizard you select Date and MDY

@Felicia400 

That is fixed by Text to Columns as above

@Sergei Baklan 

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!

@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.