Forum Discussion
Converting date formatted as text to date
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.
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.
- 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.
