Forum Discussion

Gary Thompson's avatar
Gary Thompson
Copper Contributor
Mar 26, 2018
Solved

Date Format in Excel

I have a large multi-year Excel file that I occasionally add more information from a vendor. I use the "01/01/18" date format, but recently their files started using a "2018-01-01". Is there a way to convert their dates back to the format I use? Thanks in advance for any advice....Gary

  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 27, 2018

    Hi Gary,

     

    If your local date format is MM/DD/YY when first records are most probably text. Try to select entire column, in ribbon Data->Text to Columns and on third stage of the wizard select Date for the field with YMD format.

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor

    Hi Gary

     

    Try this:

    1. Highlight the cells/columns that contain the Dates.

    2. Right click and choose Format Cells.

    3. Choose Date in the left side menu.

    4. Under Date, ensure that you select the format that you want (Might also be worth checking the locale to ensure that the dates are displayed in the right order for your region).

     

    Select the appropriate format and click OK.

    This should hopefully do the trick.

     

    Hope it helps.

     

    Cheers

    Damien

     

    • Gary Thompson's avatar
      Gary Thompson
      Copper Contributor

      I have tried that already, and it did not work. I have attached an example of part of the file that shows

      the 2 different formats that are in the date column. I appreciate your help...Gary

       

      • Stanislav Ivanov's avatar
        Stanislav Ivanov
        Copper Contributor

        You can modify the date as you want using the DATE function to strip the components of a string and join them back as the date you want - see an example.

        In my example the

        • year  is RIGHT(TEXT(A2,"dd/mm/yyyy"),4)
        • month - MID(TEXT(A2,"dd/mm/yyyy "),4,2)
        • day - LEFT(TEXT(A2,"dd/mm/yyyy"),2)

          A2 is the date I strip into components

          If excel recognizes all your entries as dates you need the text function

          If theay are strings just right(A2,4) is enough,you do not need the text function - TEXT(A2,"dd/mm/yyyy") 

         

         



        I use this to convert form US format when not recognized  - see sample

        I

Resources