Forum Discussion
Microsoft excel date format-not changing at all
Look, if you use formula like
=CONCATENATE(TEXT("a","YYYY-MM-DD"),TEXT("b","YYYY-MM-DD"),TEXT("c","YYYY-MM-DD"))it returns exactly the same as
=CONCATENATE("a","b""c")i.e. "abc". TEXT() function changes nothing in value if it is text, doesn't matter which format do you use. In the file practically all data are texts in form of dates. Dates in Excel are sequential integers where 1 is equal to Jan 01, 1900. You may apply any format to the text "07/28/2020", Excel will keep it as text "07/28/2020". But if you enter in any cell number 44040 and apply to it "YYYY-MM-DD" format, it will be shown as date 2020-07-28. Behind it's still will be number, not text.
Thus first you shall convert texts to dates as it was explained earlier. With that you may concat without any formatting if only you need to have strings to compare, like
Dear Sir,
I am trying to apply text to columns on the New Data Tab of the excel sheet that i have sent- but it is not being converted to dates.
I have used the exact method-: select column of the excel sheet>Data tab on the ribbon>Text to columns>Next>Next>choose YMD>Finish,
I even selected the columns one at a time. The data on the New Data Excel Tab hasn't shown any change. Can you please confirm if something is wrong with the dates on the New Data Tab
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
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.
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
You shall not use MDY for all sheets and all columns. It depends on how initial texts are build.
For that column you shall use MDY
For the column as below that will be YMD
If in between in column you have already converted dates, don't care about them and use the format of origin for texts only, here is MDY
Dear Sir,
I am trying to apply text to columns on the new data tab of the excel sheet that i have sent- but it is not being converted to dates.
I have used the exact method-: select column of the excel sheet>Data tab on the ribbon>Text to columns>Next>Next>choose YMD>Finish,
I even selected the columns one at a time.4th Oc,2020
Dear Sir,
for sheet tab-Old Data: I have used text to columns-MDY for all the columns.
However, the dates remain in the YMD format, as per the attachment that I am sending-date format 2 excel file.for sheet tab New Data-in date format 2 excel file: i am converting all the dates into MDY using text to columns.
However, these date are displaying the YMD format even after converting it into MDY.There are many dates that are not matching,in the Tab-Old Data & Tab-New Data, even though I am using MDY for both the sheets. i have only highlighted a few of them
The date formats of both the sheets should match, actually.Sheet tab- Result:I have concatenated, then used "IF statement".
.Please have a look at it. I cannot figure out why few of the dates are showing such behaviour.date format 2 is the sheet where I have used the MDY format using Text to columns.
date format is the sheet which has the original (unformatted data).- OK thx for the info 🙂
Nikolino NikolinoDE , IMHO, that doesn't matter, only the choice of MDY, DMY, whatever, matters.
- Hello Mr. Baklan, as far as I have seen the first two columns in the worksheet with the date have a nomenclature Africaans, maybe that is the problem?
... that the first two columns have a different bit scheme than the third.
Nikolino In your case it shall be not YMD, but MDY. Here is format of origin, to the one you'd like to have.
lotusana003 , I have exactly the same settings. Perhaps you have texts instead of dates.
Take a look at Sergei' second post, second image again.
You must make sure it is MDY
The rows with failed conversions in your exempe files have “days” that are bigger than 12 so I guess you used DMY.Dear Sir,
Could you please send a screen shot of this settings window on your computer. This is the screen shot of the settings on my computer. I don't know whether there is any issue in the settings because of which the date formats are not changing.
It works, please check attached. Hard to say what was wrong in your case.
