Course outline / Date and time functions
Lesson 42 of 75 · Intermediate · 4 exercises
Working with time
Build times, take out hours and minutes, and work out hours worked.
What you'll practise
- Build a time with TIME
- Take out the hour
- Hours worked
- Pay for the hours
Build a time with TIME
Excel stores a time as a fraction of a day: 12:00 is 0.5, and 6:00 is 0.25. TIME builds one from hours, minutes and seconds.
In cell E2, build the time 17:30 with TIME, then subtract the start time in A2.
Take out the hour
HOUR and MINUTE pull the parts out of a time.
In cell E2, find the hour of the finish time in B2.
Hours worked
Subtracting times gives a fraction of a day. Multiply by 24 to turn it into hours.
**In cell E2, work out the hours from A2 to B2: (B2-A2)*24.**
Pay for the hours
In cell E2, work out the pay: the hours from A2 to B2 multiplied by the hourly rate in C2.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.