join date & time & add hours

[Guest]

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

 

[David Biddulph]

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.

 

[Guest]

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]

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

 

[Guest]

thanks. Got it

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