how can I set the concatenate column as a date time column

**newcryout** · 09-03-2009, 05:40 AM

hi guys. I 'm trying to concatenate the date and time together as the a new datetime format. but it seems that the date time cann't be set to as datetime format. (I change the column's date format , but the date-time column didn't change according to my selected option) how can i set this column. and make the excel take it as a date column?

A2=date
B2=time
date-time=CONCATENATE(TEXT(A2,"MM-DD-YYYY"),TEXT(B2," HH:MM"))

date time date-time
8/29/2009 0:00:00 08-29-2009 00:00:00
8/29/2009 0:15:00 08-29-2009 00:15:00
8/29/2009 0:30:00 08-29-2009 00:30:00
8/29/2009 0:45:00 08-29-2009 00:45:00

Even I 've format the cell in date-time column as date. and select a date type. but the column value didn't change according to my selection.

**DonkeyOte** · 09-03-2009, 05:44 AM

So I think you're saying the Date column aren't being treated as date values, correct ?

If so highlight dates in Column A -> Data -> Text to Columns -> Either Delimited/Fixed Width -> proceed to Step 3 choose Date format of MDY -> Finish.

In your third column your formula should now work though in reality you could just use:

C2: =SUM(A2:B2)

And format result per whichever date format you want rather than storing the resulting datetime stamp as a text string.

**DonkeyOte** · 09-03-2009, 05:51 AM

As mentioned at Mr E

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.mrexcel.com/forum/showthread.php?t=413769

**newcryout** · 10-01-2009, 03:20 AM

sorry! I don't know this is in the same forum. cause I first post a question and didn't get reply. so I think maybe I can get help from other place. And I definitely don't know that you are the boss of these two forum.

if the www.excelforum.com are the same as www.mrexcel.com?

http://www.mrexcel.com/forum/showthr...50#post2049650

By the way. the solution is good. thank very much!