Convert text date to mmddyyy 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!
 
D

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top