Date FunctionsIntermediate

TIMEVALUE Function

A clock time the sheet will actually do arithmetic with.

Task:

The tracker exports each van's arrival as text, so you cannot subtract one from another to get a duration. Convert each arrival to a real time value in column C.

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.

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

4 rows × 3 columns3 cells you fill in
ABC
1VehicleArrived (text)Arrival time
2Van 108:15
3Van 210:30
4Van 314:45

Functions used here

What this exercise teachesMay contain the answer

Text that looks like a time is not a time, and the difference only shows up when you try to use it: subtracting one text arrival from another gives an error rather than a duration. Converting on the way in is what turns an export into something you can measure with.