Date Functions
Intermediate

Compare text times with TIMEVALUE

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

Task:

Store opening is 09:00. The tills export each opening time as text. In C2:C6 show Late for any store that opened after 09:00, and On time for the rest.

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 × 3 columns5 cells you fill in
ABC
1StoreOpened (text)Status
2High St08:55
3Market Sq09:12
4Retail Park09:00
5Station10:05
6Harbour8:58
What this exercise teachesMay contain the answer

As text, "8:58" sorts after "10:05" because 8 comes after 1, which would mark Harbour late. Converting both sides to time values compares them as clock times, and exactly 09:00 is not late.