How to Join/concatenate a date field with a time field in Excel?

A

Alan

I have another application that exports to Excel where column A has the
date and column B has the time. I want to be able to combine the two fields
in a 3rd field, say column C, where the time reads like it does when you
enter in the command =now(). The date format is correct, but the time comes
in text format, not time format.Can anyone help me with a formula that
combines the 2 cells to create a correct Excel formatted date & time cell??

TIA, Alan
 
D

Doug Kanter

Take a look at the Concatenate function, which allows you to join values
from multiple cells into one.
 
B

Bob Phillips

I would have said

=TEXT(A1,"dd mmm yyyy")&" "&TEXT(B1,"hh:mm:ss")

but you said the date is okay so maybe

=A1&" "&TEXT(B1,"hh:mm:ss")

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

I think I'd try to make the time entries really times.

Maybe just selecting that range and
edit|replace
what: : (colon)
with: : (colon)
replace all

Would make excel see it as time.

Then you could just use
=a1+b1

Alternatively, you could try something like:
=a1+timevalue(b1)
 
A

Alan

The problem is that the concatenate function doesn't deal with the
formatting properly so it's wrong.


Sample: I have shown the date and time with the date formatting off in the
1st line and on on the second line to show you that the date is stored as
an accepted date number. The time column is straight text entry.

COMMENTS TranDate TranTime DATE&TIME
general format >> 38239 13:29 8/9/05 16:57
mmddyy format >> 09/09/04 13:29

Under column C (DATE&TIME) I want to concatenate A and B so that the answer
is also in the accepted numerical equivalent so that it can be formatted
like it appears in the example.

Am I making sense?

Alan
 
Top