Convert text date to mmddyyy date

[wx4usa]

I have 3 columns containing the date. A=Month (Text-January) B=Day
(Numeric 1-31) C=Year. I need to combine these 3 columns into one
column in the date format mm/dd/yyyy.

Thanks for your help!

 

[Dave Peterson]

Try this in D1:
=--(B1&A1&C1)
and drag down
Format the range as a date.

that concatenation results in a string that looks like:
12January2008

The first minus coerces the date into a number (but a negative number). The
other minus changes it to a positive number.

And since dates are just plain old numbers (formatted nicely) in excel, it may
even work!

 

[T. Valko]

This can be tricky depending on your international location because there
are so many international date formats.

I think this one will work everywhere that uses the date names Jan, Feb,
Mar, etc.

Create this defined name...

Insert>Name>Define
Name: Months
Refers to: ="JanFebMarAprMayJunJulAugSepOctNovDec"
OK

Then use this formula:

=DATE(C1,CEILING(SEARCH(LEFT(A1,3),Months)/3,1),B1)