# Track elapsed hours in Google Sheet

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

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

# 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```
• 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. 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. 👍 Jan 20 at 17:56