Forum Discussion

denhoule's avatar
denhoule
Copper Contributor
Oct 23, 2021
Solved

Converting date formatted as text to date

I have exported a file from a website which downloads into a csv file.  The dates are shown as mm/dd/yyyy and is left aligned which confirms that it is text and not in date format.  I want to apply a...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Oct 23, 2021

    denhoule 

     

    Select entire column B;

    On the Data ribbon, choose Text to columns;

    Next;

    Next;

    Now in Step 3 of 3, select the Date button and choose MDY from the list;

    Finish.

     

    Now the text dates are numbers and can be formatted however you like. See attached.

     

    By the way, your sheet was set to "Show formulas" on the Formula ribbon. Switched it off to get see the proper results. Copied the birthday texts to the side to check that the US style dates transformed to Canadian style correctly.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 23, 2021

denhoule 

 

Select entire column B;

On the Data ribbon, choose Text to columns;

Next;

Next;

Now in Step 3 of 3, select the Date button and choose MDY from the list;

Finish.

 

Now the text dates are numbers and can be formatted however you like. See attached.

 

By the way, your sheet was set to "Show formulas" on the Formula ribbon. Switched it off to get see the proper results. Copied the birthday texts to the side to check that the US style dates transformed to Canadian style correctly.

denhoule's avatar
denhoule
Copper Contributor
Oct 23, 2021
Thank you Riny......the point I was missing was in the first step....when the text to columns opened it was set at delimited and I never changed it to fixed width. In the next step I checked off Other as the delimiter and inserted "/" so I got the data in 3 columns....and I didn't know what to do next.
When I saw your sheet and that it worked, it prompted me to think a little more, and the light finally came on. When I changed from delimited to fixed width, it worked like a charm.
Thank you again.

Resources