A shift that runs 23:00 to 07:00 subtracts to minus 16 hours. Sheets is not confused. It answered a different question from the one you meant to ask. This page shows what a time actually is inside a spreadsheet, the one function that fixes the arithmetic, how to take an unpaid break off it, which calendar day a night shift belongs to, and the two things a month grid cannot work out no matter what you write in it.
Same layout as the seven mistakes page, with one column added. Change the ranges to match yours, but change them everywhere.
A2:A15 shift code, B start time, C end time, D length in hours (a formula, never typed), E unpaid break in whole minutes. B and C hold real times, not text.
B1:AF1 holds the dates of the month as real dates, B2:AF25 is the grid. A shift code goes in the column of the day the shift starts, which matters more than it sounds and is settled further down.
A time in Google Sheets is not a label. It is a fraction of one day, stored as a plain number, and everything on this page follows from that.
Midnight is 0. Noon is 0.5. Any time is the hour divided by 24. So
23:00 is 23/24, which is 0.958333, and 07:00 is
7/24, which is 0.291667. You can see this yourself: type a time in an empty
cell, then Format, Number, Number. The clock face disappears and the number
underneath shows up.
Now do the subtraction the obvious way. C2-B2 is
0.291667 minus 0.958333, which is -0.666667 of a day, and times 24 that is
-16 hours. Nothing went wrong. You asked how far 07:00 is from 23:00 on the
same day, and the honest answer is sixteen hours backwards. What you meant was the
next 07:00, and nothing in the cell says so.
This is the single most expensive error in a hand-built work schedule, because it does not look like an error. It looks like a minus sign. Feed a column of them into a monthly total and every night shift you ran comes out under-reported on the timesheet you hand to payroll.
One function. Put it in Shifts!D2 and it fills the column.
=ARRAYFORMULA(IF(A2:A15="","",MOD(C2:C15-B2:B15,1)*24))
MOD(x,1) throws away the whole-day part of a number and keeps the
remainder, and in Sheets the remainder takes the sign of the divisor. The divisor
here is 1, which is positive, so -0.666667 comes back as 0.333333,
and times 24 that is 8 hours. The negative got folded forward into the next
day, which is exactly what an overnight shift does in real life.
Day shifts are untouched. A 09:00 to 17:00 gives 0.333333 straight away, already
between 0 and 1, and MOD hands it back unchanged. You do not need two
formulas or an IF to spot night shifts. One formula covers both.
A shift whose start and end are identical returns 0, not 24. The remainder of a whole day is nothing. If you genuinely run 24-hour shifts, split them into two codes.
And a shift longer than 24 hours is invisible to this. A 26-hour span reads as 2 hours, and no arithmetic on a start time and an end time can tell those two apart, because the two shifts store identical numbers. If you roster anything past a full day, the length has to be typed by hand in column D rather than derived.
Keep the break in whole minutes in Shifts!E. Thirty, not 0:30.
=ARRAYFORMULA(IF(A2:A15="","",MOD(C2:C15-B2:B15,1)*24-IF(E2:E15="",0,E2:E15)/60))
A night shift of 23:00 to 07:00 with a 30 minute unpaid break is 8 minus 0.5, so
7.5 paid hours. The IF in the middle is there so a blank break
cell reads as zero instead of dragging the whole row into an error.
Minutes rather than a time value is a deliberate choice. A break typed as
0:30 is 0.0208 of a day, and the moment you have one column in
day-fractions and another in hours you will subtract one from the other and get an
answer that is wrong by a factor of 24. One unit per column, written in the header.
A break longer than the shift produces negative paid hours and says nothing. So does the identical-start-and-end case above. Catch both at once: select Shifts!D2:D15, Format, Conditional formatting, Custom formula is:
=AND(D2<>"",D2<=0)If the break is paid, it does not belong in this column at all. Paid break time is hours the person is owed.
This is a real decision with two defensible answers, and it is not a formatting question. It moves money.
Someone works 23:00 Sunday to 07:00 Monday. Record it on the day it started and those 8 hours land in the week that is ending. Record it on the day it finished and they land in the new week.
Put numbers on it. Say that person already has 46 hours in the old week and the weekly limit is 48. Under the start-day rule they finish on 54, which is six hours of overtime. Under the end-day rule the old week stays at 46, nothing crosses the limit, and the 8 hours open a new week that has room for them. Same person, same shift, same eight hours worked. The convention decided whether you owe overtime.
Payroll almost always writes the shift to the day it started, and that is what this page and the mistakes page both assume: one code, in the column of the start date, never split across two cells. The genuine failure mode is not picking the wrong rule. It is a sheet where some rows follow one rule and some follow the other, which happens quietly whenever a second person starts filling in the rota. Write your choice in a cell on the sheet where the next person will see it.
Some agreements pay a premium only for hours actually worked after midnight, so you need the split rather than the total. Hours falling before midnight:
=MIN(MOD(C2-B2,1),1-B2)*24
For 23:00 to 07:00 the length is 0.333333 and 1-B2 is 0.041667, one
hour, so MIN picks the hour: 1 hour before midnight. For 09:00
to 17:00 the length is 0.333333 and 1-B2 is 0.625, so
MIN picks the length and returns all 8 hours, which is right,
because none of that shift crosses anything. The rest of the night shift is the
total minus that:
=MOD(C2-B2,1)*24-MIN(MOD(C2-B2,1),1-B2)*24Seven hours after midnight. Both are single-cell formulas, so drag them down the column, and both assume a shift under 24 hours. Neither takes the break off, on purpose. Which side of midnight a break falls on is a decision somebody has to make, not something the sheet can work out.
A length is enough for pay. It is not enough for rest, and rest is where the overnight shift causes its second round of trouble.
To know how long someone was off you need the exact moment a shift ended, date and time together, and for a night shift that moment is on the following calendar day. With the date in A2 and the start time in B2, the start moment is just the two added:
=A2+B2and the end moment is that plus the length from column D, converted back into a fraction of a day:
=A2+B2+D2/24For 10 September 2026 plus 0.958333 plus 8/24, the fraction crosses 1 and the whole number ticks over on its own. The result is 11 September 2026, 07:00. You do not have to add a day anywhere. Dates and times are the same kind of number, which is the one genuinely elegant thing about how Sheets stores them.
One trap. The cell will show a bare date, or a five-digit decimal, until you set Format, Number, Custom date and time and give it both parts. People decide the formula failed and delete it at this point. It did not fail, it is just being displayed as a date. Once you can see both, the gap between two shifts is the subtraction. With last night's end moment in G2 and tonight's start moment in H2, the rest in hours is:
=(H2-G2)*24That is the whole basis of a rest check, and the mistakes page shows what it costs to build across a full month grid. Short version: four mirror sheets, and it still only compares each day with the day immediately before it.
Weekly limits have the same trouble one level up: the week that belongs to two months.
Every formula above assumes B and C hold numbers. When they hold text that looks like a time, some of it errors and some of it just goes quiet.
The tell is alignment. Numbers and dates sit against the right edge of a cell by
default, text sits against the left. If 07:00 is hugging the left
edge it is a string. That test fails if somebody set the alignment by hand, so the
reliable version is =ISNUMBER(B2), which answers FALSE
for text no matter how it is aligned.
Text times arrive from a leading apostrophe, a CSV import, a paste out of another system, or a column that was formatted as Plain text before anyone typed in it. Count them in one cell, anywhere:
=COUNTA(A2:A15)-COUNT(B2:B15)
COUNTA counts every shift row that has a code. COUNT
counts only cells holding a number, ignoring text and blanks. Any answer other
than zero is the number of rows whose start time is not a number, whether it is
text or simply missing. Both are rows you need to look at. Do the same with
C2:C15 for the end times. To see which rows, conditional format
Shifts!B2:C15 with:
=AND(B2<>"",NOT(ISNUMBER(B2)))To rescue them, put this in a spare column and drag it down, then copy the result and paste back over the originals with Paste special, Values only:
=IF(ISNUMBER(B2),B2,IFERROR(TIMEVALUE(B2),""))
TIMEVALUE reads text that your spreadsheet's locale recognises as a
time. 23:00 and 11:00 PM normally convert.
2300 and 23.00 depend entirely on the locale, and when
they are not recognised this returns a blank, which is the point of the
IFERROR. The blanks are your retype-by-hand list.
Worth knowing why this is worth chasing. An error is the good outcome, because you
can see #VALUE! from across the room. The bad outcome is a column of
hours with a few text entries in it, because SUM skips text without
complaining. The month total comes out short, nothing is red, and the first
person to notice is whoever got underpaid.
The 31st is the awkward one. A shift starting 23:00 on the last day of the month ends at 07:00 on a date that is not on this sheet.
The pay is fine. Under the start-day rule those 8 hours belong to the 31st, they sit in the last column, and the month total is correct. Three other things are not fine, and no formula on this sheet can fix any of them, because the data they need lives in a different file.
Rest is the first. The gap between that shift finishing at 07:00 and whatever the person works on the 1st is unmeasurable here, and the 1st is exactly the day a short turnaround is most likely, because whoever built next month's rota was not looking at last month's last row.
Then the run of days worked without a break, which restarts at column B whether or not the person worked the previous eleven days. And then the week itself: a week running Thursday to Wednesday across the boundary is half on one sheet and half on the other, so a weekly limit check sees two short weeks instead of one long one and reports neither.
I would not pretend to solve that with a formula. The two honest options are to check the boundary week by hand once a month, which takes about ten minutes and you will forget, or to stop using a grid.
Stopping means one row per shift, with a real date-and-time start and a date-and-time end in their own columns, everything in one continuous table. Overnight shifts stop being a special case, because the end really is on the next day rather than pretending to be. Rest becomes a sort and a subtraction. There is no month boundary because there are no months. What you lose is the reason you built a grid in the first place: seeing the whole month at once, who is on Tuesday, where the hole is. That is a real trade and not an upgrade, which is why most people who try the flat table go back to the grid.
| Formula | Goes in | Assumes |
|---|---|---|
MOD(C-B,1)*24 | Shifts!D2, filled down | shift under 24 hours |
- E/60 on the end | the same cell | break is unpaid, in whole minutes |
MIN(MOD(C-B,1),1-B)*24 | a spare column | you need the hours split at midnight |
A2+B2+D2/24 | a date-time helper column | D is already in hours, cell formatted as date and time |
COUNTA-COUNT | anywhere | answer other than 0 means text in your time column |
If you would rather not maintain the column, this is the part ShiftSheet handles in the same spreadsheet.
It reads the start and end times off your Shifts sheet, and when the end is not
after the start it adds a day before working out the length, which is the same
trick as MOD with one difference worth flagging: a shift whose start
and end are identical comes back as 24 hours rather than 0. It then subtracts the
unpaid break in minutes from that column of your Shifts sheet. Rest between shifts
is measured from actual finish moments rather than by calendar day, so an overnight
shift is compared against the next morning properly. 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
and prints every timesheet in one go.
Also on the Google Workspace Marketplace. Either route runs the same code. The other guide on this site covers the seven things that go wrong in a shift schedule and which of them a formula can catch.