1

I have a table in Google Sheets that I use to track the time elapsed between a TIME IN and a TIME OUT.

As you can see below, my TOTAL formula in D3 works, calculating the elapsed time from 5 AM to 3 PM as 10 hours. My formula in D5 fails when calculating 7 PM to 2 AM, returning 17 hours instead of the correct elapsed time of 7 hours.

  1. TIME IN and TIME OUT columns are formatted as automatic (time).
  2. The TOTAL column is explicitly formatted as duration [h]:mm:ss
  3. The formula in D3 is
    =ABS(B3-C3)
  4. The formula in D5 is
    =ABS(B5-C5)

Why does the formula in D5 return the wrong number of hours?

A   B   C   D  
1
2 TIME IN TIME OUT TOTAL
3 JIB 5:00 AM 3:00 PM 10:00:00
4 DIE 0 0 0:00:00
5 MA 7:00 PM 2:00 AM 17:00:00

enter image description here

1
  • I added an ARRAYFORMULA version to my answer. It calculates multiple rows with a single formula.
    – Blindspots
    Jan 21 at 2:33

1 Answer 1

0

TL;DR

You need to adapt your formula to add 1 day (24 hours) to any OUT time that is less than the IN time.

Formula

  1a.  Basic Duration Formula

= C3 + (C3  < B3) - B3          // Out + ([0]or[1] day) - In
                           // See 'F3' in the table.

  1b.  Return Number

= (C3 + (C3 < B3) - B3) * 24   // Multiply by 24 to return a number 
                          // instead of a duration. See 'G4' in table

  2.  Array Formula Duration Formula

Same as the Basic Duration Formula but will do the calculation for multiple rows. Includes a basic ISNUMBER test to ensure that both IN and **OUT** contain numbers.

=ARRAYFORMULA(
   IF(ISNUMBER(B3:B) * ISNUMBER(C3:C), 
      C3:C + (C3:C < B3:B) - B3:B, ))

FUNCTIONS:  ARRAYFORMULA  IF  ISNUMBER

Compare Number Formats

Impact of different number formats on the same values.

A              B C D E F G                                 
1 Format In Out C – B C + 1 E – B F * 24
2 Time    h:mm am/pm 7:00 PM 2:00 AM 7:00 AM 2:00 AM 7:00 AM 12:00 AM
3 Duration    [h]:mm 19:00 2:00 -17:00 26:00 7:00 168:00
4 Number  #,##0.000 0.792 0.083 -0.708 1.083 0.292 7.000

Explanation

  1. Sheets stores times (and durations) as the equivalent number of days.
    For example:
    = "2:00:00" + 0       // 2 hrs is 1/12 of 24 hrs
    =  0.08333...
     
    = "2:00 AM" + 0
    = 0.08333...           // 2 AM is 1/12 of the way through a day
  2. Given the way Sheets stores times, for any given time, the equivalent number should be 1 larger (a day) than the same time on the previous day:
    Monday 2 AM - Sunday 2 AM = 1 (day)
  3. Whenever OUT  falls on the next day, 1 needs to be added:
    # 2 AM (Monday) - 7 PM (Sunday)
    =  2/24  +  1  -  19/24 =  7/24 = 0.29166... // Number = 7:00:00 // Duration = 7:00 AM // Time
  4. Sheets interprets boolean values as 1 or 0 in calculations. Since your durations are always less than 24 hours, the result from the boolean test OUT < IN  can be added to OUT which will effectively add 1 whenever OUT falls on the next day:
    +----------------------------+
    | FORMULA     = OUT + (OUT < IN) - IN    |
    +----------------------------+
    +----------------------------+ | TRUE IN:  7 PM, OUT:  2 AM      | +----------------------------+ =   2/24  +  ( 2/24  <  19/24 )  -  19/24 =   2/24  +  1  -  19/24 =   0.29166... // Number =  7:00:00 // Duration =   7:00 AM // Time
    +----------------------------+ | FALSE IN:  5 AM, OUT:  3 PM      | +----------------------------+ =   15/24  +  ( 15/24  <  5/24 )  -  5/24 =   15/24  +  0  -  5/24 =   0.41666... // Number =  10:00:00 // Duration =  10:00 AM // Time
2
  • 1
    Wow, thank you so much! It was a bit confusing since im new to sheets formulas. I didnt know you could use ">" or "<" within paranthesis WITHIN arithmetic operations! Thank you again, and no, the time span wont be more than 24 hours.
    – ZenLife18
    Jan 20 at 9:05
  • @ZenLife18 Np! I'm sure I learned that on this site and try to share back the tips others taught me. Do continue to participate on this site and pay it forward. 👍
    – Blindspots
    Jan 20 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.