Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Choice of Date or another field | Excel Discussion (Misc queries) | |||
Convert date to length of time in months from set date | Excel Worksheet Functions | |||
Time "and" Date for data points | Charts and Charting in Excel | |||
US vs UK date and time formats | Excel Discussion (Misc queries) | |||
Help wtih date and time in the same cell | Excel Worksheet Functions |