2 columns to 1

[lynn]

i want to make 2 columns 1. I know how to do this, but
going to a 3rd column, and do =A1&B1. The problem is,
column A contains a date, column B contains the time. I
want the answer to appear as 12/1/02 07:45:00. When I
join the columns together with the formula above, the time
formatting is lost. I end up with a number. Is it
possible to join 2 fields like this and retain the
formatting ? Using either Excel 2000 or 2002

 

[Ken Wright]

If your dates and times are real ones, and are exactly as shown, then just = A1+B1. Excel treats
times as a fraction of a day, so by adding a fraction of a day to a date, you will get the date
and time together. If it doesn't appear that way, then just choose a format such as dd/mm/yyyy
hh:mm

 

[Rafael Ortiz]

Do this:

=A1+B1

Then format the cell as desired: Format, Cells, Custom, and type in
the following custom format:

m/d/yyyy hh:mm:ss

I think that should do it.

MRO

 

[Ken Wright]

Hi Alan, that reply was pretty much the same as mine, and on a straight copy and paste of his data
from his note into two cells, it works fine for me. Depends on his data I know, but as long as it
is as he states in his note with 12/1/02 (Effectively being 12/1/02 00:00:00) in one cell, and
07:45:00 in another, then I don't know why it wouldn't work. Didn't test any further than that
though, so may well have missed something.

 

[Rafael Ortiz]

I always test my solutions with Excel. This is an obvious (to long
time users of Excel like me) solution because I know Excel treats
dates and times as serial numbers. So the addition of these 2 cells
should produce the desired solution.

But it does assume your date and time are formatted as date and time.

MRO

 

[John Wilson]

Ken & Alan,

What might have been missed (on the OP's part but as he didn't answer back
we'll never know if he solved his problem).

Anyway, from the OP's post, he was using
=A1&B1
If A1 wasn't formatted as a date but B1 was formatted as a time, he'd get
the date (or what looked like a date) followed by a decimal number.
If both A1 & B1 are formatted correctly,
=A1+B1
should give him the correct result.

John