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!!!

"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.

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.
.

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!!!