Concatenate Date+Time+Am or PM then sum hours

**bjsmithinc** · 06-14-2013, 03:12 AM

Have six columns: "Date in" "Time In" "AM or PM" "Date Out" "Time Out" "AM or PM"

What I'm trying to get to from these columns is the amount of hours passed from time in to time out. It could be anywhere from a few hours to weeks.

I keep getting the dreaded #value error when I take the difference of my concatenated data(and I'm probably doing that wrong also).

Thanks all!

**benishiryo** · 06-14-2013, 03:22 AM

hi bjsmithinc, welcome to the forum. try this in D5:
=A5+(B5/100&" "&C5)+0

format accordingly to what you require. copy it to H5. then I5 is a simple:
=H7-D7
if you need to just see hours, custom format as:
[h]
with minutes:
[h]:mm

**martindwilson** · 06-14-2013, 03:49 AM

no good just dividing b5 by 100 ,i know its 800 formatted as ??:?? dividing by 100 gives 8 fine for whole hours but if it was 08:30 then you'd get 8.3 hours which is not the same as 8.5 hours ie 30 mins =0.5 hours
so i suggest
d5 =IF(C5="PM",A5+VALUE(TEXT(B5,"00"":""00"))+0.5,A5+VALUE(TEXT(B5,"00"":""00")))
h5==IF(G5="PM",E5+VALUE(TEXT(F5,"00"":""00"))+0.5,E5+VALUE(TEXT(F5,"00"":""00")))
then i5 =H5-D5 formatte [hh]

**benishiryo** · 06-14-2013, 04:01 AM

thanks for pointing that out, martin.

try this alternative too, modified using martin's formula:
=IF(C5="PM",A5+TEXT(B5,"00\:00")+0.5,A5+TEXT(B5,"00\:00"))

**bjsmithinc** · 06-14-2013, 02:56 PM

Thanks to you both. Working perfectly!!

**bjsmithinc** · 06-16-2013, 01:55 PM

Thought things were working, but as I was trying various times I noticed some problems. There seems to be an issue when midnight is selected (e.g. 12:00 A.M.)
This workbook will see a lot of midnight so I need it to work correctly.
Thanks again for all help!!

**martindwilson** · 06-16-2013, 04:40 PM

if midnight is 12 am
=IF(C5="PM",A5+TEXT(B5,"00\:00"),A5+TEXT(B5,"00\:00")+0.5)
or better still use 24 hour clock
nope thats wrong i just realise it will mess everything up!