Scheduling basics
Weekly schedule template in Excel: the night shift that worked minus 16 hours
By Yaseen Khatib, , 5 min read

Short answer. A weekly schedule template in Excel needs three formulas: hours per shift code, hours per person and people per shift per day. The trap is a night shift that crosses midnight: end minus start gives minus 16 hours for 22:00 to 06:00. Wrap the difference in MOD(...,1) and it gives 8.
The first sheet anyone builds for a weekly schedule is a grid: people down the side, days across, and a shift in each cell. The second thing they build is a total of hours per person, and that is where the night shift bites.
The free employee schedule template on this site is built by a Python script, and the hours column is the place where a mistake hides, because the totals still look like numbers. A night shift runs from 22:00 to 06:00. Subtract the start from the end in Excel, as you would for a day shift, and you get a negative number.
The arithmetic
Excel stores a time as a fraction of a day. 22:00 is 22 divided by 24 and 06:00 is 6 divided by 24. End minus start is 6/24 minus 22/24, which is minus 16/24, so multiplied by 24 hours it gives minus 16. A person on a night shift would show as having worked minus 16 hours, and the weekly total would quietly come up short.
The fix is one function. In the template the Shifts sheet works out hours with MOD of the difference and 1, times 24. MOD wraps a negative fraction round to the next day, so minus 16/24 becomes 8/24, and 8/24 times 24 is 8 hours. A day shift is unaffected, because its difference is already positive.
How the template is built
There are four sheets: instructions, a Shifts sheet, a Weekly grid and a Monthly grid. The structure is the part you can copy to build your own weekly schedule template in Excel.
- Shifts sheet: one row per shift code, with a name, a start and an end. The template ships with M (morning, 06:00 to 14:00), D (day, 09:00 to 17:00), E (evening, 14:00 to 22:00), N (night, 22:00 to 06:00), OFF and L for leave. The hours column is the MOD formula. Because times are typed as text such as 22:00, the formula reads them with TIMEVALUE.
- Weekly grid: one row per employee, one column per day, one shift code in each cell, chosen from a drop-down so that a typo such as NIGHT or Nt cannot slip in. Hours per person use SUMPRODUCT with COUNTIF across the shift codes and their hours. Shifts and days off are counted with COUNTA, COUNTIF and COUNTBLANK.
- Coverage rows under the grid: for each shift and each day, a COUNTIF of how many people hold that shift. This is what makes a gap visible before the week starts.
- Monthly grid: the same idea across 31 columns, with the month's first day typed once in cell B2 so the day names follow.
What the example week shows
The template opens with an example week for eight people. Check the coverage rows and you can see something I like to point out: the night shift has one person on every day except Thursday, when it has two, because the person on Monday to Thursday nights and the person on Thursday to Sunday nights overlap. Hours come out as 40 for five of the eight people and 32 for the three who work four shifts.
None of this is clever. It is the point of the coverage rows: an uneven day shows up as a number you can read, and you did not have to count cells by eye.
A checklist to build or check your own
- Put shift codes, start, end and hours on their own sheet and point every formula at it, so a change in one place changes the whole workbook.
- Work hours out with MOD(end - start, 1) times 24, so a night shift is positive.
- Use a drop-down list for the codes. Typos are the most common reason a total is wrong.
- Add a coverage row for every shift, with one COUNTIF per day, and highlight zero in red with conditional formatting.
- Count days off including blanks, otherwise an empty cell looks like a working day.
- Insert new employees above the total row so the totals keep covering everyone.
- Save one copy per week and keep the one you published.
What a spreadsheet will not do
The instructions sheet in the template says it itself: a spreadsheet records a schedule, it does not choose one. It will not pick who takes an open shift, check skills or explain a choice. If you want the week filled for you from days off and a weekly maximum, use the free schedule generator and paste the result into the grid. Our longer note on where Excel scheduling breaks explains the limits.
And if the problem you actually have is who is right for a piece of work, not who is free on Tuesday, that is what FitRank is for. See the pricing page for how a pilot works.
How do I calculate hours for a night shift in Excel?
Use MOD of the end time minus the start time and 1, then multiply by 24. For 22:00 to 06:00 this gives 8, where a plain subtraction gives minus 16.
Is there a free weekly schedule template for Excel?
Yes. The employee schedule template on this site is a free .xlsx download with a weekly and a monthly grid, shift codes, hours per person and coverage by formula, and it needs no sign-up.
How do I count how many people are on each shift?
Use COUNTIF over the day's column with the shift code, one row per shift. Add a red highlight for zero so an empty shift stands out.
Can Excel fill the schedule automatically?
Only with the Solver add-in for small teams, and it needs a model you build yourself. For a quick fill from days off and weekly limits, a browser generator is simpler.
More from the blog
Be one of the first teams to staff with FitRank
We're taking on a small number of pilot companies. Pre-register for early access, or talk to us about an annual licence.