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 

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

    • 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 

Resources