total time calculation

[Ozyborn]

         A              B          C          D             E              F               G

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

Total time between 2 cells  D and F

Format cells in D,F        Custom     [h]:mm;@

Format cell in G  time 13:30

Simple G formula    =F3-D3

Works fine am-am, pm-pm, am to pm all work fine

However pm to am leaves just ######## into of total time

Have not done anything with formulas I several years, very rusty. Need total time in G

[#2 · Accepted solution]

@Ozyborn 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. 

=end-start will result in a negative number that can not be represented in the [h]:mm format.

Use =MOD(end-start,1) for all your calculation as it will give the correct answer in all situations.

Attached a file with some examples.

[#3]

Thank you. Saved me a few hours on mind numbing searching my books.