SOLVED

New Contributor

# total time calculation

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

# Re: total time calculation

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

# Re: total time calculation

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