Date Functions
Intermediate

A time as decimal hours for billing

1:30 is 1.5 hours, not 1.3.

Task:

Consultants log time as hours and minutes, but invoices bill decimal hours at the rate in B1. In C4:C8 give each entry in decimal hours, and in D4:D8 the amount to bill.

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 HOUR, MINUTE, SECOND

0/3

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

  1. HOUR, MINUTE, and SECOND Functions

    Extract time components from time values.

    Core
    Beginner
  2. A time as a total number of minutes

    Hours × 60 plus minutes — the reverse of TIME.

    Beginner
  3. A time as decimal hours for billing

    1:30 is 1.5 hours, not 1.3.

    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.

8 rows × 4 columns10 cells you fill in
ABCD
1Rate per hour80
2
3EntryTimeHoursAmount
4E11:30
5E20:45
6E32:20
7E43:00
8E50:12
What this exercise teachesMay contain the answer

Reading 1:30 as 1.3 under-bills by 12 minutes on every such entry. Dividing the minutes by 60 gives the true fraction, and keeping decimal hours in their own column makes the invoice easy to check.