Need to concatenate a time cell with another

July 24, 2011 at 16:03:42
Specs: Windows XP
I have 2 cells that I need to join.

Example: A1 = MR and B1 = 9:15 AM - I need C1 to have MR - 9:15 AM

I keep getting MR - 0.38541666666

See More: Need to concatenate a time cell with another

Report •

July 24, 2011 at 17:01:55
Excel stores dates and times as numbers. The integer portion is the date and the decimal portion is the time.

0.38541666666 * 24 = 9.25, or 9 and a quarter hours into a 24 hour day. i.e. 9:15

CONCATENATE is "text function" in that whatever is returned is considered to be text by Excel. When you concatenate a time value with anything else, Excel no longer sees it as a time and converts it to it's numerical equivalent and displays it as text.

You'll need to extract the various parts of the time and build the text string so that it looks the way you want it to look. Note however, that the "time" displayed in C1 won't really be a time but merely a text string that looks like a time.

=A1&" - "&IF(HOUR(B1)>11,HOUR(B1)-12,HOUR(B1))&":"&MINUTE(B1)&IF(HOUR(B1)>11," PM"," AM")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question