total time calculation

Question

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; E&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; F&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; G3061681191:25pm44131.9 at 4:05pm2hr 40 minyes30616810204:08pm44131 at 5:35pm1hr 27 minyes3061689204:08pm44&nbsp;&nbsp;&nbsp;306168821&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;306168621&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;306168522&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Total time between 2 cells&nbsp; D and FFormat cells in D,F &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Custom&nbsp;&nbsp;&nbsp;&nbsp; [h]:mm;@Format cell in G&nbsp; time 13:30Simple G formula&nbsp;&nbsp;&nbsp; =F3-D3Works fine am-am, pm-pm, am to pm all work fineHowever pm to am leaves just ######## into of total time&nbsp;Have not done anything with formulas I several years, very rusty. Need total time in G

riny_van_eekelen · Accepted Answer

Ozyborn&nbsp;You need to use a slightly different formula to calculate the duration, allowing for a start time before midnight and an end time after midnight.&nbsp;&nbsp;=end-start will result in a negative number that can not be represented in the [h]:mm format.&nbsp;Use =MOD(end-start,1) for all your calculation as it will give the correct answer in all situations.&nbsp;Attached a file with some examples.

Forum Discussion

total time calculation

2 Replies

Resources

306168	1	19	1:25pm	44	131.9 at 4:05pm	2hr 40 min	yes
306168	10	20	4:08pm	44	131 at 5:35pm	1hr 27 min	yes
306168	9	20	4:08pm	44
306168	8	21
306168	6	21
306168	5	22