Forum Discussion

lotusana003's avatar
lotusana003
Copper Contributor
Sep 23, 2020

Microsoft excel date format-not changing at all

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 ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 23, 2020

lotusana003 

That's exactly as HansVogelaar 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:

new sheet:

 

  • lotusana003's avatar
    lotusana003
    Copper Contributor
    Sep 24, 2020

    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  ,SergeiBaklan 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 24, 2020

      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")

       

       

       

      • lotusana003's avatar
        lotusana003
        Copper Contributor
        Sep 26, 2020

         

        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.

         

        SergeiBaklan 

Resources