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

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

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

"Alan" wrote in message
...
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

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)


"Alan" wrote in message
...
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

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)


Alan wrote:

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

--

Dave Peterson

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


"Doug Kanter" wrote in news:vB7Ke.4069$j21.3174
@news01.roc.ny:

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

"Alan" wrote in message
...
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