If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
using & to concatenate result is yuck
Am trying to concatenate to cells using & the result is
incorrect. trying to join: '2004.04.26 and 00:01:00 and the result is: '2004.04.26 0.000694444444444444 desired result is: '2004.04.26 00:01:00 If I insert an ' in front of 00:01:00 then the result is correct. However I have 8000 records to join. either I need a way to insert ' in front of each of the 8000 records or I need a way to concatenate without the result turning to crap. HELP!!! |
#2
|
|||
|
|||
using & to concatenate result is yuck
"Jamie" wrote...
Am trying to concatenate to cells using & the result is incorrect. trying to join: '2004.04.26 and 00:01:00 and the result is: '2004.04.26 0.000694444444444444 desired result is: '2004.04.26 00:01:00 ... Time values are stored as fractions of days no matter how they happen to be formatted, and formulas that refer to any cells containing numeric values, including times and dates, as if they were text use whatever the numeric value would appear as if formatted as General. If you want time values in time format, use TEXT. =A1&TEXT(A2," [hh]:mm:ss") -- To top-post is human, to bottom-post and snip is sublime. |
#3
|
|||
|
|||
using & to concatenate result is yuck
Thank you so much for the text formula on concatenating
with leading zeros. This works perfectly. Thanks again. -----Original Message----- "Jamie" wrote... Am trying to concatenate to cells using & the result is incorrect. trying to join: '2004.04.26 and 00:01:00 and the result is: '2004.04.26 0.000694444444444444 desired result is: '2004.04.26 00:01:00 ... Time values are stored as fractions of days no matter how they happen to be formatted, and formulas that refer to any cells containing numeric values, including times and dates, as if they were text use whatever the numeric value would appear as if formatted as General. If you want time values in time format, use TEXT. =A1&TEXT(A2," [hh]:mm:ss") -- To top-post is human, to bottom-post and snip is sublime. . |
#4
|
|||
|
|||
using & to concatenate result is yuck
Jamie
Just a thought. An alternative with no Concatenation. Enter 04/26/2000 as a valid date in a cell. Format the column as Custom yyyy.mm.dd hh:mm:ss In cell below enter =cellref + 1/1440 Drag/copy down the column to increment by 1 minute. Gord Dibben Excel MVP On Wed, 7 Apr 2004 17:20:25 -0700, "Jamie" wrote: Am trying to concatenate to cells using & the result is incorrect. trying to join: '2004.04.26 and 00:01:00 and the result is: '2004.04.26 0.000694444444444444 desired result is: '2004.04.26 00:01:00 If I insert an ' in front of 00:01:00 then the result is correct. However I have 8000 records to join. either I need a way to insert ' in front of each of the 8000 records or I need a way to concatenate without the result turning to crap. HELP!!! |
Thread Tools | |
Display Modes | |
|
|