SOLVED

Converting date formatted as text to date

%3CLINGO-SUB%20id%3D%22lingo-sub-2876227%22%20slang%3D%22en-US%22%3EConverting%20date%20formatted%20as%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2876227%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20exported%20a%20file%20from%20a%20website%20which%20downloads%20into%20a%20csv%20file.%26nbsp%3B%20The%20dates%20are%20shown%20as%20mm%2Fdd%2Fyyyy%20and%20is%20left%20aligned%20which%20confirms%20that%20it%20is%20text%20and%20not%20in%20date%20format.%26nbsp%3B%20I%20want%20to%20apply%20a%20formula%20to%20those%20birthdates%2C%20but%20of%20course%2C%20the%20formula%20doesn't%20recognize%20it%20as%20a%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20the%20DATEVALUE%20function%2C%20text%20to%20columns%2C%20nothing%20seems%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%20of%20the%20problem%20might%20be%20that%20I%20am%20in%20Canada%2C%20and%20the%20format%20is%20US%2C%20so%20dates%20that%20are%2007%2F20%2F1944%20won't%20work%20because%20Canadian%20format%20is%20dd%2Fmm%2Fyyyy%20and%20there%20is%20no%2020th%20month....but%20even%20the%20dates%20like%2005%2F02%2F1947%20won't%20convert%20to%20date%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20file.%26nbsp%3B%20It%20originally%20downloaded%20as%20a%20csv%2C%20but%20I%20saved%20as%20a%20workbook%20hoping%20that%20might%20help....it%20didn't.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20somone%20look%20at%20this%20file%20and%20come%20up%20with%20a%20solution%20that%20works%3F%26nbsp%3B%20%26nbsp%3BMuch%20appreciate%20any%20help%20on%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDennis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2876227%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2876260%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20date%20formatted%20as%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2876260%22%20slang%3D%22en-US%22%3EI'm%20having%20a%20similar%20issue%20with%20the%20ages...they%20are%20formatted%20as%20text%2C%20but%20i%20cannot%20convert%20to%20a%20number.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2876267%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20date%20formatted%20as%20text%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2876267%22%20slang%3D%22en-US%22%3EIgnore%20the%20number%20issue%20with%20age%20in%20above%20post...I%20was%20in%20formula%20view%20when%20I%20tried%20to%20change....I%20can%20convert%20age%20to%20a%20number%2C%20but%20still%20stuck%20on%20the%20date%20issue.%3C%2FLINGO-BODY%3E
Occasional Contributor

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

4 Replies
I'm having a similar issue with the ages...they are formatted as text, but i cannot convert to a number.
Ignore the number issue with age in above post...I was in formula view when I tried to change....I can convert age to a number, but still stuck on the date issue.
best response confirmed by denhoule (Occasional Contributor)
Solution

@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.

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.