Microsoft excel date format-not changing at all

Copper Contributor

I download an excel file(.xls) format from a particular portal that has been designed by the IT developers in my company. the downloaded excel sheet displays a date format which cannot be changed at any cost. i have tried custom format, text to columns,short date.. every possible method.. even used formulas like 

=TEXT(H2,"YYYY-MM-DD"), but to no avail. There are basically 2 excel data sheets from 2 different portals that need to be matched, and for matching the data sheets, the date formats of the 2 data sheets should match as well along with the additional criterias. And this is a problem which has surfaced,only on my Windows excel 2010 version. Please help.

29 Replies

@lotusana003 

As variant you have texts, not dates, but better to check sample file with few records.

@lotusana003 

The values are probably text instead of dates. Could you attach a small sample workbook without sensitive information?

I am sending an excel sheet with only the date columns for the new and old data sheet. Basically I have to concatenate each of the cells and then match the final concatenated data using the "IF" statement. But since the date formats for both the sheets differ, so the end result is a mismatch. Please have look.. I was wondering if had something to do with the settings of my Windows Excel. @Hans Vogelaar 

@lotusana003 

The values are text indeed. You can convert each of the columns to 'real' dates as follows:

  • Select a column with dates, e.g. A2:A158.
  • On the Data tab of the ribbon, click 'Text to Columns'.
  • Click 'Next >' then 'Next >' again.
  • In Step 3, select YMD from the Date dropdown.
  • Click Finish.

S3538.png

You should now be able to compare the dates.

I tried that, but it's the dates are not matching.. @Hans Vogelaar 

I ma sending a sample file with the dates. Please have a look. @Sergei Baklan 

@lotusana003 

That's exactly as @Hans Vogelaar said. It works column by column (you can't apply wizard at once for more than one column). And you have different source format in new and old sheets. In attached file I converted for Column A only

old sheet:

image.png

new sheet:

image.png

 

Dear Sir,

The date format in the old data sheet can be changed, the date format in the new data sheet is creating problems. If you could perform concatenation in each of the sheets using the formula-

=CONCATENATE(A2,B2,C2,TEXT(D2,"YYYY-MM-DD"),TEXT(E2,"YYYY-MM-DD"),F2,TEXT(G2,"YYYY-MM-DD"),TEXT(H2,"YYYY-MM-DD"),I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2). I am sending the worksheet where I have performed a calculation. Please have a look,I basically want to match the date formats  ,@Sergei Baklan 

@lotusana003 

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.

 

@Sergei Baklan 

@lotusana003 

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

image.png

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

 

@Sergei Baklan 

@lotusana003 

It works, please check attached. Hard to say what was wrong in your case.

@lotusana003 

 

What the previous ones, who very well known of the subject - Excel, told you is absolutely correct.

Just add the columns to the format where you wanted, as I understood.

 

If this is not what you are asking for, please ignore my message.

 

Have a nice day.

Nikolino
I know that I know nothing (Socrates)

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. 

 

lotusana003_0-1601646240675.png

 

@Sergei Baklan 

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.

@lotusana003 , I have exactly the same settings. Perhaps you have texts instead of dates.

@lotusana003 

In your case it shall be not YMD, but MDY. Here is format of origin, to the one you'd like to have.

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