Dates and times are proper data types in a spreadsheet, not just text that looks like a clock. Once they are entered correctly, you can calculate hours on a shift and how long a Work Experience week really runs. That is the skill behind a timesheet an employer or supervisor can trust.
Think of one real block of time from your project or Work Experience: a placement day, a shift, a training session, or an event set-up. In your head, note the start time, the end time, and roughly how many hours that was. You will turn that kind of record into a working spreadsheet today.
Three ideas unlock today's tracker. Read the table, then check the worked sample so you know what done looks like.
| Concept | Why it matters | Example |
|---|---|---|
| Real date — a calendar date the spreadsheet can sort or compare (not typed words) | The sheet must see a real date, not words, or later maths and stamps break | Type 03/03/2026 for a Work Experience start. Wrong looks left-aligned like plain text; right looks like a proper date |
| Real time — a clock time entered with a colon (09:00), so the sheet can do maths with it | End time minus start time only works when both cells are true times, not text such as 9-00 | A Centra stocktake shift from 09:00 to 13:30 becomes hours with one formula |
| Hours formula — {{formula:=(end-start)*24}}, then format the result as Number | Multiply by 24 to get hours you can read. Format as Number (not Time) or you may see 04:00 or a tiny decimal instead of 4 | {{formula:=(D2-C2)*24}} on a timesheet row, then Number format so the cell shows 4.00 |
Your finished tracker will look like this shape (use your own dates, places, and times):
| Date | Location | Start | End | Hours |
|---|---|---|---|---|
| 03/03/2026 | Work Experience: reception | 09:00 | 13:00 | 4.00 |
| 04/03/2026 | Work Experience: stockroom | 09:30 | 14:00 | 4.50 |
| 05/03/2026 | Project set-up | 10:00 | 12:30 | 2.50 |
| 06/03/2026 | Work Experience: phones | 09:00 | 15:00 | 6.00 |
| 07/03/2026 | Project follow-up | 11:00 | 13:00 | 2.00 |
Column E uses a formula, not typed numbers. A total row under the five entries adds the hours automatically. You will also stamp the sheet with {{formula:=TODAY()}} so it shows when you last checked it.
Build a small sample hours tracker with five rows of date and time data, a formula for hours worked, Number format so hours read clearly, a total, and a TODAY() check. Use the sample placement data first so every formula is proven before you switch to your own Work Experience or Something Real project times.
| Issue | Solution |
|---|---|
| Hours show as a tiny decimal or as a clock time like 04:00 instead of 4.00 | Select the Hours cells (including the total), set the number format to Number with 2 decimal places (not Time), and keep the formula as end minus start, then multiply by 24 |
| A date will not work with TODAY() or looks wrong | Re-enter the date as a real date value and apply a Date number format. If the cell is left-aligned plain text, the sheet is not treating it as a date |
| End minus start gives a negative number, or the cell shows ##### | ##### usually means the column is too narrow or the result is invalid. Widen the column, check that end is later than start on the same day, and that both cells use a Time format with a colon (09:00 not 9-00) |
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.