Forum Discussion
Microsoft excel date format-not changing at all
I'm sorry, but I didn't find where is the formula from the post within workbooks. It has lot of sheets and different columns, not clear to what and which formula you'd like to apply. To illustrate just couple of records in one sheet will be enough.
If you apply formula like TEXT(D2,"YYYY-MM-DD") for the D2 which has text value, it will do nothing. You may simply use D2, result will be the same. If you'd like to present the text in another format, first you shall convert it to date (aka number) and after that use TEXT() with new format. You may convert entire column first, or within formula. If in your locale default date format is the same as in text, you may use TEXT(VALUE(D2),"YYYY-MM-DD"). If not, first parse the text and convert to date then, like TEXT(DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,4,2)),"YYYY-MM-DD")
sheet tab-new data sheet.
it displays columns that have dates from the new sheet.I have used the formula "=CONCATENATE(TEXT(A2,"YYYY-MM-DD"),TEXT(B2,"YYYY-MM-DD"),TEXT(C2,"YYYY-MM-DD"))".
The date format does not change.
the column labeled new concat- displays concatenated data for all the 3 cells.
Sheet tab-old data sheet
it displays the columns that have date formats from the old data sheet.
I have used the same formula "=CONCATENATE(TEXT(A2,"YYYY-MM-DD"),TEXT(B2,"YYYY-MM-DD"),TEXT(C2,"YYYY-MM-DD"))".
the column labeled old concat- displays concatenated data for all the 3 cells.
Sheet tab-result:the old concatenated data and new concatenated data are placed side by side.
then the condition is applied: =IF(A2=B2,1,0).
All the results are displaying 0, even though the dates are same, but formats are different.
My aim is to match the results, i.e to bring the output as 1.
Please help me to do so.
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 TabIn your case it shall be not YMD, but MDY. Here is format of origin, to the one you'd like to have.