Brackwell Facilities guarded a distribution centre for the whole of November: 38 shifts, 19 of them overnight. Payroll paid £108.50 for the month. The rota was worth £3,468.50, and not one time on the timesheet was typed wrong.
The total cell held the right value. It displayed 7:45, because the cell was formatted h:mm, and a clock format has nowhere to put the 240 hours that came before. The clerk keyed in what the screen said.
| Measure | As paid | As worked |
|---|---|---|
| Shifts | 38 | 38 |
| Overnight shifts | 19 | 19 |
| The total cell | 7:45 | 247:45 |
| Keyed into payroll | 7.75 | 247.75 |
| Paid at £14.00 an hour | £108.50 | £3,468.50 |
A time in Excel is a fraction of a day, and a day is where h:mm stops counting. The hashes down the night shifts, the pay that came out a twenty-fourth of itself and the total that wrapped are all the same fact, met three times.
01The Total That Started Again at Midnight
Eight Shifts, Four of Them Hashed
Eight shifts from Brackwell Facilities' November rota, sitting in A2:H9. Column B is the clock-in and column C the clock-out, both as times of day. Column D is the subtraction anybody would write, =C2-B2, which is correct on the four day shifts and prints ###### on the four that cross midnight. Column E is the same subtraction wrapped in =MOD(C2-B2,1). Column F is that value multiplied by 24, so the hours can be added, compared and paid, and column G is column F at £14.00 an hour. These eight shifts come to 74.75 hours. The full month holds 38 shifts, 19 of them overnight, and comes to 247.75 hours — 247:45 in a cell formatted [h]:mm, and 7:45 in a cell formatted h:mm.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Column D is the subtraction anyone would write: =C2-B2, the end of the shift minus the start. It is right on every day shift and prints ###### on every night one.
Column E is the same subtraction wrapped in MOD, and column F is that value multiplied by 24 so the hours can be added, compared and paid. These eight shifts come to 74.75 hours; the month comes to 247.75.
The sheet that went to payroll had no column E. The night rows had been retyped by hand, the total sat in a cell formatted h:mm, and the number on the screen was a tenth of the number in the cell.
Scenario: Put =SUM(E2:E9) under your own paid-time column and format the result as [h]:mm. If the figure jumps by 24 hours or more the moment you add the square brackets, the cell was wrapping and somebody's month was short.
02A Time Is a Fraction of a Day
Excel stores a date as a whole number of days and a time as the fraction left over. Midday is 0.5. One hour is 1/24, one minute is 1/1440, one second is 1/86400.
That is why 8:30 and 0.354167 are the same cell in two formats. Multiply a time by 24 and you have hours as a number you can pay: =0.354167*24 is 8.5.
| On screen | Stored value | × 24 |
|---|---|---|
| 0:15 | 0.010417 | 0.25 |
| 8:30 | 0.354167 | 8.50 |
| 12:00 | 0.500000 | 12.00 |
| 24:00 | 1.000000 | 24.00 |
| 247:45 | 10.322917 | 247.75 |
TIME(8,30,0) writes that fraction without anybody typing decimals, and it is the safe way to put a duration inside a formula. TIME wraps at 24 hours, though: =TIME(26,0,0) returns 2:00, not 26 hours.
For anything longer than a day, write the division instead. Twenty-six hours is =26/24, which is shorter, still readable six months later, and never wraps.
Scenario: Type 8:30 in a cell, then =A1*24 beside it and format that one as a plain number. Expect 8.5. Then put =TIME(26,0,0) underneath and watch it come back as 2:00.
03Why the Night Shifts Printed
A shift from 22:00 to 06:30 is 0.916667 minus 0.270833, which is −0.645833. Excel cannot display a negative time in the 1900 date system, so it fills the cell with hashes instead.
Widening the column does nothing, because the column is not too narrow. The value is negative, and ###### is Excel saying so in the only way a time format can.
MOD fixes it in one wrap:
=MOD(C2-B2,1)
MOD returns the remainder after division, and a negative numerator comes back positive: =MOD(-0.645833,1) is 0.354167, which is 8:30. On a day shift the subtraction is already positive and MOD leaves it alone.
One formula covers both, so the whole month totals in a single cell: =SUMPRODUCT(MOD(C2:C39-B2:B39,1))*24 returns 247.75. That is the hours, as a number, with no format standing between the sheet and the answer.
Scope.
MODassumes no shift runs longer than 24 hours. A 26-hour standby would come back as 2 hours, silently, so pair the fix with a check:=COUNTIF(E2:E39,">=1")should be zero.Excel's 1904 date system does allow negative times, and a workbook switched to it shows −15:30 rather than hashes. Switching shifts every existing date in the file by four years and a day, so it is a setting to recognise rather than one to turn on.
Scenario: Enter 22:00 and 06:30 in two cells, subtract one from the other and watch the hashes appear. Wrap the same subtraction in =MOD(C2-B2,1) and confirm 8:30 before you trust any total built on that column.
04The Square Brackets That Keep Hours Counting
h:mm shows a time of day, so it reports a total the way a clock would: 247 hours and 45 minutes becomes 7:45, because 240 hours is ten whole days and a clock throws days away.
[h]:mm keeps them. The square brackets tell Excel not to roll the unit over, and the same cell reads 247:45. Nothing about the stored value changes; only the number a human reads off the screen.
Set it on the total cell: Ctrl+1, Number, Custom, then type [h]:mm into the Type box. [mm] does the same for minutes — 14,865 of them here — and [ss] for seconds.
| Format | 247:45 shows as | Use it for |
|---|---|---|
h:mm | 7:45 | A clock time |
[h]:mm | 247:45 | A duration |
[mm] | 14865 | Minutes worked |
| General | 10.32 | Nothing in payroll |
The formula was never wrong here, and that is what made it expensive. A #VALUE! gets investigated the same morning; a plausible 7:45 at the bottom of a month of night cover gets keyed into payroll.
Scenario: Select your month total, press Ctrl+1, choose Custom and type [h]:mm. Read the figure before and after. The gap between them is a multiple of 24 hours, and it is the pay somebody did not get.
05Hours Into Money: the ×24 Nobody Writes
Multiplying a time cell by a rate pays a twenty-fourth of the wage. =E2*14 on an 8:30 shift returns 4.958, which rounds to £4.96, because E2 holds 0.354167 of a day rather than 8.5 hours.
The 24 goes in before the rate:
=ROUND(MOD(C2-B2,1)*24*14,2)
ROUND to two decimals belongs there too. Pay is money, and an unrounded £119.0000000001 behaves fine on screen and badly in a total somebody reconciles to the penny.
| Shift | Paid time | =E2*14 | =E2*24*14 |
|---|---|---|---|
| 8:00 | 0.333333 | £4.67 | £112.00 |
| 8:30 | 0.354167 | £4.96 | £119.00 |
| 12:15 | 0.510417 | £7.15 | £171.50 |
| 247:45 | 10.322917 | £144.52 | £3,468.50 |
Keep one decimal-hours column and pay from that. Once column F holds 8.50 rather than 8:30, every rate, premium and reconciliation downstream of it is ordinary arithmetic.
Scenario: Add =F2*14 beside your decimal hours and =E2*14 beside the time values. Check both against one shift with a calculator; the second is always the first divided by 24.
06Rounding Punches to the Nearest Quarter Hour
Most timesheets round to a quarter of an hour. Round the punches, not the total: rounding once at the bottom hides thirty-eight small differences inside one number nobody can check.
MROUND snaps a time to the nearest multiple:
=MROUND(B2,TIME(0,15,0))
A punch of 22:07 becomes 22:00 and 22:08 becomes 22:15. Running the same formula on the clock-out keeps the pair consistent, which matters more than which way any single punch moved.
One-sided rounding is CEILING and FLOOR. =CEILING(B2,TIME(0,15,0)) pushes a clock-in forward to the next quarter and =FLOOR(C2,TIME(0,15,0)) pulls a clock-out back, which is the employer's side of every boundary.
Check the contract before you pick one. Rounding is a term of employment rather than a formatting preference, and both UK and US practice expect it to come out neutral over time instead of always in the employer's favour.
A grace period is a different thing again.
=IF(ABS(B2-$J$1)<=TIME(0,5,0),$J$1,B2)treats any punch within five minutes of the rostered start as on time, and leaves a genuinely late one alone.
Scenario: Put =MROUND(B2,TIME(0,15,0)) beside a punch of 22:07 and expect 22:00. Change the punch to 22:08 and expect 22:15. Then compare your month total before and after rounding; the difference should be under an hour.
07Unpaid Breaks Come Off Before Overtime
A 30-minute unpaid break is a subtraction, and TIME writes it: =MOD(C2-B2,1)-TIME(0,30,0). On a 12:15 shift that leaves 11:45 paid.
The break usually depends on the length of the shift, so the deduction has to be conditional rather than typed into every row:
=LET(worked, MOD(C2-B2,1),
brk, IF(worked>TIME(6,0,0), TIME(0,30,0), 0),
MAX(0, worked - brk))
LET names the subtraction once and uses it three times, so a correction lands in one place. MAX(0,…) stops a short shift going negative and bringing back the hashes section 3 got rid of.
Deduct the break first, then split the overtime. The other order pays a premium on half an hour the guard spent eating, and across 19 night shifts that is nine and a half hours at time and a half.
Scenario: Build the LET version in a spare column and test it on three shifts: a five-hour one, a six-hour one and the 12:15 Wednesday. Expect no deduction on the first two and 11:45 on the third.
08Splitting Basic Hours From Overtime
Once the hours are decimal, the split is two functions and no nesting. Basic is =MIN(F2,8) and overtime is =MAX(0,F2-8).
They always add back to the shift, which is the property a nested IF chain loses the first time somebody edits a threshold in one row and not the next.
| Shift | Paid hours | =MIN(F2,8) | =MAX(0,F2-8) | Pay at £14 and £21 |
|---|---|---|---|---|
| Mon day | 8.00 | 8.00 | 0.00 | £112.00 |
| Mon night | 8.50 | 8.00 | 0.50 | £122.50 |
| Wed day | 12.25 | 8.00 | 4.25 | £201.25 |
| Sun day | 12.00 | 8.00 | 4.00 | £196.00 |
Weekly overtime is the same shape against a week's total: =MAX(0,SUM(F2:F8)-40). Pick daily or weekly, never both, or the hours past eight on Wednesday get paid a premium twice.
Scenario: Add the two columns to your sheet and put =SUM(G2:G39)+SUM(H2:H39)-SUM(F2:F39) underneath. It has to be zero. Anything else means a threshold typed into one row and not into the one below it.
09Totals per Guard, Not per Timesheet
A month total is for the invoice. The questions that come back are about people: who worked the 19 nights, who is over 48 hours in a week, who was paid for a shift they swapped away.
SUMIFS adds times as happily as it adds money, and the ×24 belongs on the outside:
=SUMIFS($E$2:$E$39,$A$2:$A$39,$J2)*24
=COUNTIF($A$2:$A$39,$J2) counts the shifts behind that figure and =AVERAGE($F$2:$F$39) gives the mean length. A guard with 9 shifts averaging 13 hours is a rota problem, and a month total on its own never shows it.
Every one of those results is a time until something multiplies it by 24, so a summary table needs the same [h]:mm treatment, or the same ×24, as the total it was cut from.
Scenario: Build a small table of names in column J and put the SUMIFS beside each one. Check that the column adds to 247.75; if it falls short, a name is spelled two ways somewhere in column A.
10When a Punch Is Text and SUM Ignores It
A punch pasted out of a door system often arrives as text: 22:00 with a trailing space, 8.30 with a full stop, or an apostrophe somebody typed to stop Excel reformatting the cell.
Text left-aligns and SUM skips it without a word. The count is the test:
=COUNT(B2:B39)
COUNT counts numbers only, so anything under 38 is a punch that is not a time. =COUNTA(B2:B39) counts everything, and the gap between the two is exactly how many cells need fixing.
=TIMEVALUE(TRIM(B2)) converts one of them: TRIM removes the trailing space and TIMEVALUE reads what is left as a time. A full stop instead of a colon needs =VALUE(SUBSTITUTE(B2,".",":")) first.
Scenario: Put =COUNT(B2:B39) and =COUNTA(B2:B39) under your punch column. If they disagree, select the column, run Text to Columns and finish on the first screen; it reparses every cell and usually closes the gap.
11Sending Hours to a Payroll System
Most payroll systems want decimal hours rather than a clock: 8.5, not 8:30. =ROUND(F2,2) is the whole export, and the column has to be formatted as a number, or the file writes 0.35.
Where the system wants HH:MM as text, =TEXT(E2,"[h]:mm") writes it with the brackets intact, so a 247:45 total survives the trip as text instead of arriving as 7:45.
HOUR wraps where the format does. =HOUR(E40) on the month total returns 7, not 247, because HOUR reports a time of day. Split a long duration arithmetically instead:
=INT(F40)&"h "&ROUND(MOD(F40,1)*60,0)&"m"
Scenario: Write =TEXT(E40,"[h]:mm") and =HOUR(E40) beside your month total. The first reads 247:45 and the second reads 7, which is the bug that started this article wearing a different hat.
12Eight Things That Bite
- A total formatted
h:mm. Right for the first ten days of a month of night cover and wrong by exactly 24 hours for every day after that. =C2-B2on a night shift. Negative, hashed, and far more often retyped by hand than fixed.- Paying a time cell.
=E2*14is a twenty-fourth of the wage, and it looks like a plausible small number rather than an error. TIME(26,0,0). Wraps to 2:00. A duration longer than a day is written26/24.HOURon a duration. It reports the clock, so a 247:45 total comes back as 7.- Rounding the total instead of the punches. One tidy number and thirty-eight differences nobody can explain.
- Breaks deducted after the overtime split. Pays a premium on an unpaid half hour, every long shift, all year.
- A punch that is text.
SUMskips it in silence, andCOUNTis the only thing in the sheet that notices.
13Mini Exercises
- Subtract
22:00from06:30in a spare cell and expect######. Wrap it in=MOD(C2-B2,1)and expect 8:30. - Total column E with
=SUM(E2:E9)and format ith:mm, then[h]:mm. Expect 2:45 and then 74:45. - Pay the month both ways with
=E40*14and=F40*14. Expect £144.52 and £3,468.50. - Round a 22:07 punch with
=MROUND(B2,TIME(0,15,0))and expect 22:00; change it to 22:08 and expect 22:15. - Split the Wednesday shift with
=MIN(F5,8)and=MAX(0,F5-8). Expect 8.00 and 4.25, and 12.25 when you add them back.
What to Take Away
Nothing on this timesheet was mistyped. Thirty-eight shifts were recorded to the minute, the subtractions were the ones anybody would write, and the cell at the bottom held exactly the right number. It displayed a tenth of it.
Time in Excel is a unit with a ceiling. A day is 1, h:mm stops at 24 hours, TIME stops at 24 hours and HOUR stops at 24 hours. Every bug in this article is one of those ceilings, met at speed.
So keep two habits and the rest follows: wrap every shift in MOD and format every total as [h]:mm. Then multiply by 24 once, pay from the decimal column, and the month comes out worth what it was worth.