time calculation 24 hour

[pile-it Mark]

hello!

i have a sheet for recording trips, yes the same sheet i have been playing with for 17 years.
P12 (formatted as text) is time (1109)
Q12 (formatted as text) is time (1207)
R12 (formatted as 13:30) is where i input the elapsed time. (0:58)
S12 (formatted as number 0.00 )has a formula that converts R12 to decimal (0:97)

P & Q are input as 24 hour UTC so the day shifts around 7pm. usually not an issue but it is possible p12 (2300) Q12 (0200) r12 (3:00). the length of time will not exceed 8 hours between P & Q,

after all these years i would like to make the calculation automatic.

Excel 2010
	P	Q	R	S
11	OFF	ON	TOTAL	HOURS decimal
12	1109	1207	0:58	0.97
13	1245	1423	1:38	1.63
14	2038	2212	1:33	1.55
15	2221	2305	0:43	0.72
16				0.00
17				0.00
18				0.00
03 23 2018

Cell Formulas
Range	Formula
S12	=IF((ISBLANK(H12))," ",HOUR(R12)+ROUND(MINUTE(R12)/60,2))
S13	=IF((ISBLANK(G13))," ",HOUR(R13)+ROUND(MINUTE(R13)/60,2))
S14	=IF((ISBLANK(H14))," ",HOUR(R14)+ROUND(MINUTE(R14)/60,2))
S15	=IF((ISBLANK(H15))," ",HOUR(R15)+ROUND(MINUTE(R15)/60,2))
S16	=IF((ISBLANK(H16))," ",HOUR(R16)+ROUND(MINUTE(R16)/60,2))
S17	=IF((ISBLANK(H17))," ",HOUR(R17)+ROUND(MINUTE(R17)/60,2))
S18	=IF((ISBLANK(H18))," ",HOUR(R18)+ROUND(MINUTE(R18)/60,2))

Excel 2010
	P	Q	R	S
11	OFF	ON	TOTAL	HOURS decimal
12	1305	1507	2:02	2.03
13	2321	0052	1:31	1.52
14				0.00
15				0.00
02 01 2018

Cell Formulas
Range	Formula
S12	=IF((ISBLANK(H12))," ",HOUR(R12)+ROUND(MINUTE(R12)/60,2))
S13	=IF((ISBLANK(G13))," ",HOUR(R13)+ROUND(MINUTE(R13)/60,2))
S14	=IF((ISBLANK(H14))," ",HOUR(R14)+ROUND(MINUTE(R14)/60,2))
S15	=IF((ISBLANK(H15))," ",HOUR(R15)+ROUND(MINUTE(R15)/60,2))

thanks,
Mark

 

[PaddyD]

the general form for an elapsed time calculation that crosses a date boundary, and so where end_time can be a smaller number than start_time (and there is no date value to help) is:

(end_time < start_time) + (end_time - start_time)

...let us know if you can't adapt to your data set up.

 

[pile-it Mark]

date is E12 based on UTC at takeoff.

i can modify the sheet data and formatting to whatever it takes to make it work. it is a form i submit as a PDF to accounting

 

[PaddyD]

shouldn't need to worry about the date - the formula I posted can cope without them.

Given your example data, does:

=(q12 < p12) + (q12 - p12)

...return the answers you expect?

 

[pile-it Mark]

yes! thank you! i had to format P & Q to time, instead of text, but so far that works great

 

[PaddyD]

glad you've got it sorted after all these years