Date Functions
Intermediate

Hours between two text times

Convert both, subtract, and multiply by 24.

Task:

The door system exports clock-in and clock-out as text. In D2:D6 give each shift's length in hours.

Interactive Spreadsheet

Read about TIMEVALUE

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of TIMEVALUE

0/3

The same formula in the other shapes it takes at work.

  1. TIMEVALUE Function

    A clock time the sheet will actually do arithmetic with.

    Core
    Intermediate
  2. Hours between two text times

    Convert both, subtract, and multiply by 24.

    Intermediate
  3. Compare text times with TIMEVALUE

    "10:05" > "9:30" is false as text; as times it is true.

    Intermediate

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 4 columns5 cells you fill in
ABCD
1StaffIn (text)Out (text)Hours
2Gita08:0016:30
3Hal09:1517:45
4Iris07:3012:00
5Jon13:0021:15
6Kat10:0018:00
What this exercise teachesMay contain the answer

Excel stores a time as a fraction of a 24-hour day, so the difference between two times is a fraction too — 0.354 for 8.5 hours. Multiplying by 24 turns it into the number a payroll sheet wants.