Convert text in multiple columns to real date and time

I have the following columns and data in Excel 2010. Here are the first few of the 3000+ rows:

Please Login or Register  to view this content.

I have been hacking away at this for 2 hours with no luck. I need to convert this data into a legitimate date and time format in a single cell, so I can use it in a Pivot Table.

How do I accomplish this seemingly simple procedure? I am confused by how the cells should be formatted, and the use of various functions like concatenate, date, dateformat, and text. It's not clear what should be set to what. I can get the numbers into a single cell using concatenate, but Excel doesn't seem to treat the numbers is a real date and time.

Also, I need to do the same thing but with no time; just the date.

Thanks to everyone for the help. This gives me the dreaded #VALUE! error:

=DATEVALUE(A2&"/"&B2&"/"&C2)+D2

This one worked fine:

=DATE(C2,MONTH(B2&A2),B2)+D2

In both cases, the cell format was set to "Date" with a "type" of "3/14/01 1:30 PM"

Thanks again! I will have to save this spreadsheet as the formula will certainly come in handy in the future.

Originally Posted by AlKey

try this

=DATE(C2,MONTH(B2&A2),B2)+D2

OK. It worked at my end. So how does appending the month reference to the day reference coerce the month number? I've never seen this one before? Should I start a new thread?

Just trying to learn.