Oct 22 2021 07:49 PM
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 formula to those birthdates, but of course, the formula doesn't recognize it as a date.
I've tried the DATEVALUE function, text to columns, nothing seems to work.
Part of the problem might be that I am in Canada, and the format is US, so dates that are 07/20/1944 won't work because Canadian format is dd/mm/yyyy and there is no 20th month....but even the dates like 05/02/1947 won't convert to date format.
I've attached the file. It originally downloaded as a csv, but I saved as a workbook hoping that might help....it didn't.
Can somone look at this file and come up with a solution that works? Much appreciate any help on this.
Dennis
Oct 22 2021 08:08 PM
Oct 22 2021 08:15 PM
Oct 22 2021 10:59 PM
Solution
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.
Oct 23 2021 07:52 AM
Oct 22 2021 10:59 PM
Solution
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.