Make a timesheet in Excel that adds up the hours
Enter start and finish times and let Excel count the hours. Why the total shows 04:00 instead of 28:00, breaks, night shifts and pay at an hourly rate.
A timesheet in Excel takes the start time from the finish time and takes off the break. SUM then adds up the hours. If the total shows 04:00 instead of 28:00, format it as [h]:mm.
Here is one week for a care worker who does day shifts and one night shift. Payroll wants the total hours and the pay.
First: a time is part of a day
Excel stores a time as a fraction of one day. 12:00 is 0.5. 06:00 is 0.25. One hour is 1/24, about 0.0417.
So type times with a colon, like 9:00 or 17:30. If you type 9, the cell holds the number 9. To Excel, that is 9 whole days. For 10 at night, type 22:00, or 10:00 PM with a space before PM.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Day | Start | Finish | Break | Hours |
| 2 | Mon | 09:00 | 17:30 | 0:30 | |
| 3 | Tue | 08:30 | 16:30 | 0:30 | |
| 4 | Wed | 13:00 | 17:00 | 0:00 | |
| 5 | Fri | 22:00 | 07:00 | 0:30 | |
| 6 | Total | ||||
| 7 | |||||
| 8 | Rate | 14.50 |
Hours for one shift
In E2, type =C2-B2-D2. It gives 8:00.
Finish minus start is 8 hours 30 minutes. Take off the 30 minute break and 8 hours are left. Fill it down and Tuesday gives 7:30 and Wednesday 4:00.
Friday fills the cell with ##### signs. The shift starts at 22:00 and ends at 07:00 the next morning. So 07:00 minus 22:00 is a negative time, and Excel cannot show a negative time.
Night shifts: MOD
In E2, type =MOD(C2-B2,1)-D2 and fill it down to E5.
Monday to Wednesday do not change. Friday now gives 8:30.
Here is how it works. 07:00 minus 22:00 is -0.625 of a day. MOD with 1 adds 1 to a number between -1 and 0. So -0.625 becomes 0.375, which is 9 hours. A positive number less than 1 stays the same.
Use MOD on every row, not only on night shifts. Then nobody has to remember which rows need it. One limit: a shift of 24 hours or more needs the date as well as the time.
| Mon | 8:00 |
| Tue | 7:30 |
| Wed | 4:00 |
| Fri | 8:30 |
Why the total shows 04:00
In E6, type =SUM(E2:E5). The week is 28 hours. But the cell shows
4:00.
The sum is right. 28 hours is 1 day and 4 hours, and the value is about 1.1667. The h:mm format shows a clock. A clock goes back to 0 after 24 hours, so it shows only the 4.
Select E6. Press Ctrl+1, click Custom, and type [h]:mm as the format. The cell now shows 28:00.
The square brackets tell Excel to keep counting hours past 24.
From hours to pay
The rate in B8 is 14.50 an hour. Many people type
=E6*B8. It gives 16.92, the pay for 1.1667 hours.
In E8, type =ROUND(E6*24*B8,2). It gives 406.
E6 is in days, so times 24 turns it into hours: 28. Then 28 hours at 14.50 is 406.00. ROUND keeps the pay to whole cents.
ROUND matters even when the number looks exact. Sums of times can leave a tiny remainder that you cannot see. ROUND removes it, so the pay is what the payslip shows.
If the pay cell shows a time instead of 406, Excel copied the time format from E6. Format the cell as Number or Currency.
Breaks typed as minutes
Some people type the break as 30, not 0:30. That is 30 days to Excel. There are 1440 minutes in a day, so divide by 1440.
With 30 in D2, =MOD(C2-B2,1)-D2/1440 gives 8:00.
Questions people ask
How do I show 7.5 instead of 7:30?
Multiply by 24. =E3*24 gives 7.5. Format the cell as Number, or
it may show as a time.
How do I work out overtime?
Say the contract is 25 hours a week. =MAX(E6*24-25,0) gives 3.
MAX stops a short week from giving negative overtime.
Why does my hours cell show ####?
Either the column is too narrow, or the time is negative. Widen the column first. If that does not help, the finish is before the start, and MOD is the fix.
Can I use TODAY or NOW in a timesheet?
Not for times you worked. NOW changes every time the sheet works out its formulas. Press Ctrl+Shift+; to type the time now as a fixed value. The dates article explains more about how Excel stores dates and times.
The timesheet project below adds overtime and pay rates on a real sheet. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.