SOLVED

total time calculation

New Contributor

 

         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 Replies
best response confirmed by Sergei Baklan (MVP)
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.

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