Overtime is counted by the week. A staff schedule is laid out by the month. Those two disagree about where a week starts, and most of that disagreement is fixable with formulas you can read. The part that is not fixable is one week a month, and there are three honest things to do about it.
Staff, Shifts and Schedule are laid out as on the seven mistakes page, with a weekly hour limit per person in Staff!D2:D25 and an hourly rate in E2:E25. Two sheets do the new work.
The mirror grid, B2:AF25 holding hours where the Schedule holds codes. Row 1, row 26 and everything from column AH rightwards get used below.
New. A2:A25 names, B1:G1 week start dates, B2:G25 hours per week, I2:I25 each person's limit. M1 and M2 are the only cells on it you type into.
Four weeks of 44, 52, 38 and 34 hours add up to 168, which averages 42. There is nothing in that total to look at, and there are four hours of overtime inside it against a 48 hour limit.
Weekly limits do not net off. A quiet week does not buy back a busy one, so the average answers a question nobody asked. The 52 is owed whatever the other three weeks did.
Keep both numbers instead of deriving one from the other. The month total is what the month cost. The weekly totals are what you owe on top of it.
Not you. A contract or a payroll run fixed it already, and the job is finding out which day it says.
It matters for anyone who works a weekend. Move the boundary from Monday to Sunday and a Saturday night shift counts towards a different week, which changes which week goes over. Nobody worked a different hour. Only the line moved.
Put the answer in Weekly!M1 as a number, because a number is what
WEEKDAY takes. Type 2 for Monday weeks or 1 for Sunday
weeks. There is a third type where Monday counts as zero, and it breaks the
arithmetic below, which adds one and assumes counting starts there.
The start of any date's week is then one subtraction:
=A1-WEEKDAY(A1,2)+1
WEEKDAY says how far into the week a date sits. Subtract that and you
land on the day before the week began, then add one to step onto it. Wednesday 9
September 2026 gives 3, so 9 minus 3 plus 1 is Monday the 7th. Change the 2 to a 1
and the same date returns Sunday the 6th. Check it rather than trusting it: run
=TEXT(A1-WEEKDAY(A1,2)+1,"ddd") over a few dates and every answer
should read Mon.
These are the ones that work. Two rows of setup and one formula per week.
Give the Hours sheet its own copy of the dates, in Hours!B1:
=ARRAYFORMULA(IF(Schedule!B1:AF1="","",Schedule!B1:AF1))
It fills B1:AF1 on its own. The IF is there so a 30 day month leaves
the last column blank instead of showing a date in 1899. Then the week key row, in
Hours!B26, dragged right to AN26:
=IF(B1="","",B1-WEEKDAY(B1,Weekly!$M$1)+1)Every column of the grid now knows which week it belongs to, and you can see it. On the Weekly sheet, B1 derives the first week start from the first day of the month:
=Schedule!B1-WEEKDAY(Schedule!B1,$M$1)+1
C1 is =B1+7, dragged right to G1. Six columns, because a
31 day month can touch six week starts, and one beginning on a Sunday under Monday
weeks is that worst case. The total goes in B2, dragged right to G and down
to row 25:
=SUMIF(Hours!$B$26:$AN$26,B$1,Hours!$B2:$AN2)One criteria row, one sum row, the same width. Any column whose week key matches the date at the top of this one gets added, so column order stops mattering.
The seven mistakes page does this with SUMPRODUCT and a hand-typed row
of Mondays. It works, and it also buries the definition of a week inside every copy
of the formula, so a wrong week means debugging three multiplied arrays. Here the
definition is one visible row you can wrap in TEXT and read back.
QUERY is the better tool for grouping and it cannot touch a grid.
QUERY reads tables where a row is a record, and in a grid the date is
not a value in the row, it is the position of the column. There is nothing to group
by. Flatten the grid and QUERY becomes the right answer, which is
option b below.
October 2026 starts on a Thursday, so its first week began on Monday 28 September. Three days of that week are on one sheet and four are on the other.
Someone works three ten hour shifts on 28, 29 and 30 September, then three eight hour shifts on 1, 2 and 3 October. September's sheet reports 30 hours for that week. October's reports 24. The real number is 54 against a limit of 48, and six hours of overtime exist that neither file can see.
Label the affected weeks first, in Weekly!B26 dragged right to G26:
=IF(OR(B$1<MIN(Hours!$B$1:$AN$1),B$1+6>MAX(Hours!$B$1:$AN$1)),"partial","whole")
A week is partial when it starts before the earliest date you hold or ends after
the latest. MIN and MAX across the whole date row rather
than the first and last cells, because a 30 day month leaves AF1 empty and a blank
cell reads as zero in a comparison. This keeps working after option a, since the
range it measures grows with the data.
Put the last seven days of the previous month in Hours!AH1:AN1 with their hours underneath, looked up by name so a hire or a leaver cannot shift everyone by a row:
=IFERROR(VLOOKUP($A2,'Sep Hours'!$A$2:$AF$25,
MATCH(AH$1,'Sep Hours'!$A$1:$AF$1,0),FALSE),0)
Rightwards, not leftwards. Inserting columns before B shifts the grid and rewrites
every range you have already written. Out at AH nothing moves, the month total
stays =SUM(B2:AF2) and excludes the carried days, and the week key row
and the SUMIF already reach across to AN.
You cannot import next month, because it does not exist yet, and you do not need to. If every sheet carries the previous month's tail, each boundary week is checked exactly once, on the later of the two sheets, while you are still building that rota and can still move a shift.
It costs you a dependency between two files and the same hours sitting in two
places, so anyone who adds both months double counts seven days. Grey the carried
columns out. Across separate files this becomes IMPORTRANGE, which
returns an error until somebody clicks allow access once. The IFERROR
giving 0 is right for a new hire and silently wrong for a
name that got
retyped.
A flat table: A the date, B the name, C the code, D the
hours, and E the week start from
=IF(A2="","",A2-WEEKDAY(A2,Weekly!$M$1)+1). No months, so no boundary,
and QUERY does the whole job in one cell:
=QUERY(A2:E500,"select E, B, sum(D) where B is not null
group by E, B label sum(D) 'Hours'",0)
One row per person per week, with no six column guess and no dragging.
QUERY types each column by majority and discards the rest, so dates
stored as text disappear from the results without an error. Check with
=COUNTA(A2:A500)-COUNT(A2:A500) and expect zero, the same test the
night shift hours page
uses on time columns.
What you lose is the grid. You can have it back as a generated view:
=IFERROR(TEXTJOIN("/",TRUE,FILTER(Weeks!$C$2:$C$500,
Weeks!$A$2:$A$500=B$1,Weeks!$B$2:$B$500=$A2)),"")Doubles come back joined with a slash. That grid is read only, so you plan the month by typing rows into a table rather than dragging codes across days, and for most people that is the sticking point rather than any of the formulas.
Thirteen periods of 28 days. Every period is four whole weeks, nothing is ever
partial, and every formula on this page becomes exact. Dates run B1:AC1 with
C1 as =B1+1, and each period starts 28 days after the last with
no drift.
You give up the calendar. A period stops matching a monthly payroll run or the question of what June cost, and one month a year holds two period ends. If your payroll is already four weekly this costs nothing. If it is monthly, you have moved the mismatch rather than removed it.
Option a for almost everyone: it keeps the grid and the monthly cost figure, takes about ten minutes once, and catches the boundary week while you can still change it. Option b if you already run more than one location, or if rest between shifts matters to you, since a flat table turns that into a sort and a subtraction. Option c only where payroll runs on four week cycles already.
A part timer contracted to 20 hours and a full timer at 48 cannot share one cell, and a hard coded 48 is how the part timer's extra shifts go unnoticed for a year.
Default in Weekly!M2, per person in Weekly!I2 dragged down to row 25:
=LET(lim,IFERROR(VLOOKUP($A2,Staff!$A$2:$D$25,4,FALSE),0),IF(lim=0,$M$2,lim))
A name missing from the Staff sheet errors, and IFERROR catches that.
A name that is on it with nothing in column D does something quieter: it comes back
as 0 rather than as blank, so a test against an empty string misses it and every
week for that person shows as over. Hence the test against zero. The price is that
a genuine limit of zero reads as no limit, so if somebody must not be rostered at
all, use a column of its own.
Select Weekly!B2:G25, then Format, Conditional formatting, Custom formula is:
=AND(B2<>"",B2>$I2)The dollar on the column and not the row is the trick. Each cell compares itself against the limit sitting on its own row, so 24 people with 24 different limits need one rule between them.
Finding the excess is arithmetic. Pricing it is a decision, and running the two together is where a spreadsheet starts paying people twice.
The excess for one person in one week is the clamp everyone reaches for:
=MAX(0,B2-$I2)Across the month, counting only the weeks you can trust, in Weekly!K2:
=SUMPRODUCT((B$26:G$26="whole")*(B2:G2>$I2)*(B2:G2-$I2))
MAX is left out of that one on purpose. It collapses a range to a
single answer instead of working cell by cell, so it will not run across six weeks
inside an ARRAYFORMULA. Multiplying by the comparison does the same
job, since a week under the limit multiplies by FALSE and contributes nothing.
Then the money, with an hourly rate in Staff column E:
=LET(rate,IFERROR(VLOOKUP($A2,Staff!$A$2:$E$25,5,FALSE),0),
B2*rate+MAX(0,B2-$I2)*rate*0.5)
Every hour is paid once at the normal rate, and the hours above the limit get a
further half on top. That is what a time and a half arrangement comes to. Writing
excess*rate*1.5 and adding it to a total that already contains those
hours pays the excess twice, and the answer looks plausible enough to survive a
glance at it.
What no formula here can tell you is which shift the overtime came out of. The excess belongs to the week rather than to any shift in it, so if your night shifts carry a premium, somebody has to decide whether those six hours were night hours. The sheet will apply that rule and will not invent it.
A check on hours, not a payroll system, and the gap between those two is wider than it looks.
No tax, no social contributions, no holiday accrual, no sick pay, no public holiday multiplier, no shift premium, no rounding rules. Overtime itself is defined differently from one country and one contract to the next: some count it by the week, some by the day above a set number of hours, some both, and salaried staff often sit under different rules from hourly staff in the same room. The 48 on this page is an example, not advice.
A rota is also only a plan. What people actually worked is a separate record, and the seven mistakes page makes the case for keeping the two apart. Every weekly total here is a forecast until attendance says otherwise, which is exactly what it is good for: seeing the 52 hour week while it can still be changed.
If you would rather not build the two extra rows and a Weekly sheet, this is the part ShiftSheet handles in the same spreadsheet.
It totals hours by week against each person's own limit if they have one, and reports whoever went over with the person and the week on each line. It has the same boundary blind spot as everything else here, for the same reason: it reads the one spreadsheet you open it in, so carrying last month's tail across is still worth the ten minutes. It is free for a team of any size, with no account or sign-up with us,and its script declares one permission, to read and write the one spreadsheet you open it in. Installed from the Marketplace, Google's install screen lists two more: your email address and your basic profile. The add-on makes no network calls at all. The $39 version adds pay rates turned into what the month cost, and every timesheet printed in one go.
Also on the Google Workspace Marketplace. Either route runs the same code. The other two guides here cover the seven things that go wrong in a shift schedule and why night shift hours come out negative.