Forum Discussion
Microsoft excel date format-not changing at all
Hello Sir,
So for different columns of the excel sheet, I will have to use different formats of origin i.e to convert the texts into proper YMD or MDY format. Then the question arises, how do I identify, which format (YMD or MDY) will be suitable for which columns of the excel sheet(that is for the conversion of the texts to dates). Will it be a trial and error method? In case of trial and error, it will be quite time consuming. Would you recommend any other method? SergeiBaklan
If we don't say Excel in which format we have data it can't know that itself. If you have 1/9/2020 Excel can't make a guess is that 9th of Jan or 1st of Sep. Thus we shall say MDY or DMY. And Ctrl+Z always returns everything back if we misprint in instruction.
You shall apply format of your source data, how texts present the dates. For example, here
if apply MDY result will be
first text won't be converted since Excel doesn't recognize it as valid MDY date.
For this sample DMY returns correct result
In general, if text is not recognizable as valid date in format you applied it won't be converted.
Yes Surely.. but I have a query. In case, for a particular column of an excel sheet, the dates that are present are texts. I use "Text to columns">use MDY(suppose). It so happens that, few dates (that are texts) get converted into MDY format, the remaining dates remain as it is(continue to be texts).
Also, in case I try out YMD format on that same column (in the hope of getting all the texts into YMD format since MDY format did not work for all the dates), but get no positive outcomes Then what do I do?
I have faced situations when MDY or YMD format (text to columns) work for only few dates(text format) in a particular column, the rest of the dates remain as texts.
Sure, you are welcome. And I'm not alone here.
Thank You so much for your help. In case I have some other issues regarding this or other issues in excel, I will get back to you.SergeiBaklan