Skip to content
FitRank

Scheduling basics

Weekly schedule template in Excel: the night shift that worked minus 16 hours

By Yaseen Khatib, , 5 min read

Weekly schedule template in Excel: the night shift that worked minus 16 hours

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.

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

  1. 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.
  2. Work hours out with MOD(end - start, 1) times 24, so a night shift is positive.
  3. Use a drop-down list for the codes. Typos are the most common reason a total is wrong.
  4. Add a coverage row for every shift, with one COUNTIF per day, and highlight zero in red with conditional formatting.
  5. Count days off including blanks, otherwise an empty cell looks like a working day.
  6. Insert new employees above the total row so the totals keep covering everyone.
  7. 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.