Date Functions
Intermediate
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.
Learning Objectives:
- Convert text to a time value with TIMEVALUE
- Recognise why a duration calculation fails on text
- Clean an export before calculating with it
Hints
Solve without hints for +5 XP
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.
| A | B | C | |
|---|---|---|---|
| 1 | Vehicle | Arrived (text) | Arrival time |
| 2 | Van 1 | 08:15 | |
| 3 | Van 2 | 10:30 | |
| 4 | Van 3 | 14:45 |
What this exercise teaches (contains 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.