Convert text date to mmddyyy date

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

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!
 
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!
 
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)
 
Back
Top