using & to concatenate result is yuck

  • Thread starter Thread starter Jamie
  • Start date Start date
J

Jamie

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!!!
 
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
Click to expand...
...

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")
 
Thank you so much for the text formula on concatenating
with leading zeros. This works perfectly. Thanks again.
-----Original Message-----
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
Click to expand...
...

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")
Click to expand...
 
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
 
Back
Top