join date & time & add hours
 

I have a column with a date (which is an actual date) and a column with a
time (which is a text value). I wish to combine these as a signle date time
value and then add 14 hrs to this.

If my date is in A1 and my time is in B2 then what formula should I use in C1?

Bruce

join date & time & add hours
 

What format is your time? If you can change that to an Excel time, then in
C1 you can use =A1+B2+14/24 or =A1+B2+TIME(14,0,0) If you can define the
format on the text in B2, it can hopefully be converted to a time.
--
David Biddulph

"Bruce" wrote in message
...
I have a column with a date (which is an actual date) and a column with a
time (which is a text value). I wish to combine these as a signle date
time
value and then add 14 hrs to this.

If my date is in A1 and my time is in B2 then what formula should I use in
C1?

Bruce

join date & time & add hours
 

Thanks David,

The times in B2 are like 2:11am etc. I think they are actually text because
if I do istext its TRUE. Is there a way to convert this to a real time value?

Bruce

"David Biddulph" wrote:

What format is your time? If you can change that to an Excel time, then in
C1 you can use =A1+B2+14/24 or =A1+B2+TIME(14,0,0) If you can define the
format on the text in B2, it can hopefully be converted to a time.
--
David Biddulph

"Bruce" wrote in message
...
I have a column with a date (which is an actual date) and a column with a
time (which is a text value). I wish to combine these as a signle date
time
value and then add 14 hrs to this.

If my date is in A1 and my time is in B2 then what formula should I use in
C1?

Bruce

join date & time & add hours
 

The reason why that wasn't being interpreted as a time was that times want a
space before the AM. It can be converted with
=--(LEFT(B2,LEN(B2)-2)&" "&RIGHT(B2,2))
or for your combination you could use
=A1+(LEFT(B2,LEN(B2)-2)&" "&RIGHT(B2,2))+14/24
--
David Biddulph

"Bruce" wrote in message
...
Thanks David,

The times in B2 are like 2:11am etc. I think they are actually text
because
if I do istext its TRUE. Is there a way to convert this to a real time
value?

Bruce

"David Biddulph" wrote:

What format is your time? If you can change that to an Excel time, then
in
C1 you can use =A1+B2+14/24 or =A1+B2+TIME(14,0,0) If you can define the
format on the text in B2, it can hopefully be converted to a time.
--
David Biddulph

"Bruce" wrote in message
...
I have a column with a date (which is an actual date) and a column with
a
time (which is a text value). I wish to combine these as a signle date
time
value and then add 14 hrs to this.

If my date is in A1 and my time is in B2 then what formula should I use
in
C1?

Bruce

join date & time & add hours
 

thanks. Got it

"David Biddulph" wrote:

The reason why that wasn't being interpreted as a time was that times want a
space before the AM. It can be converted with
=--(LEFT(B2,LEN(B2)-2)&" "&RIGHT(B2,2))
or for your combination you could use
=A1+(LEFT(B2,LEN(B2)-2)&" "&RIGHT(B2,2))+14/24
--
David Biddulph

"Bruce" wrote in message
...
Thanks David,

The times in B2 are like 2:11am etc. I think they are actually text
because
if I do istext its TRUE. Is there a way to convert this to a real time
value?

Bruce

"David Biddulph" wrote:

What format is your time? If you can change that to an Excel time, then
in
C1 you can use =A1+B2+14/24 or =A1+B2+TIME(14,0,0) If you can define the
format on the text in B2, it can hopefully be converted to a time.
--
David Biddulph

"Bruce" wrote in message
...
I have a column with a date (which is an actual date) and a column with
a
time (which is a text value). I wish to combine these as a signle date
time
value and then add 14 hrs to this.

If my date is in A1 and my time is in B2 then what formula should I use
in
C1?

Bruce