What this duty roster format is for
This is the general-purpose duty roster: a grid of employees against days, with a shift code in each cell, for any department that runs more than one shift a day. It is used by hospital wards, hotel front desks, restaurant kitchens, retail stores with morning and evening shifts, factory maintenance teams and 24-hour support desks. If you have ever drawn a roster on a whiteboard or sent it as a WhatsApp photo, this is the same thing with totals that work.
It is deliberately simpler than a 3-shift rotation template. There is no fixed pattern built in; you assign codes freely, and the sheet tells you two things you cannot see on a whiteboard: how many of each shift every employee has been given, and how many people are on each shift every day. Those two numbers catch nearly every rostering mistake.
It works well for departments of 5 to about 40 people. Larger departments or multiple locations are better handled with one sheet per department consolidated separately, or with a roster tool; see the how to make a duty roster in Excel guide for the full method.
What is inside: Roster and Codes sheets
The Roster sheet has a header with Department and Week or Month. Each row is one employee: Employee, Role (Staff Nurse, Receptionist, Line Operator, Team Lead), then Day 1 to Day 31. Codes are M (morning), E (evening), N (night), G (general or day shift), WO (weekly off) and L (leave). Timings for each code live on the Codes sheet and can be anything; a hotel might set M as 07-15, E as 15-23 and N as 23-07, a store might only use M, E and WO.
After Day 31 come four per-employee totals: M Count, E Count, N Count and WO Count, each a COUNTIF over the employee's day cells. For row 6 with days in columns C to AG: =COUNTIF(C6:AG6,"M") and so on. A fifth column, Total Duties, adds M, E, N and G. These totals show at a glance whether nights are distributed fairly and whether every employee has their four weekly offs.
Below the employee rows are per-day coverage rows: one each for Morning, Evening, Night and General, counting staff on that shift for that date using =COUNTIF(C6:C25,"M") down the column. A Required row above each lets you type the minimum staff needed per shift, and a conditional format turns the coverage cell red when it falls below Required. The Codes sheet holds the code list, timings and the meaning of each code for printing alongside the roster.
- Rows are grouped by role so coverage per role can be read on the printout
- Weekend columns are shaded so weekly offs are easier to place
- Total Duties should equal working days in the month for a full-time employee; a shortfall means a missing code
How to fill it step by step
Set the Codes sheet first: decide your shifts and their timings and write the Required staff per shift into the Required rows on the Roster. Then paste the employee list with roles. Fill weekly offs before shifts, staggering them so two people in the same role are not off on the same day, and keeping each person's off on a consistent weekday where the work allows it.
Fill leave that is already approved, then assign shifts. Work column by column (day by day) rather than row by row, watching the coverage rows at the bottom: when Morning shows the required number, move to Evening and Night. Finish by scanning the per-employee totals for fairness: nobody should carry all the nights, and everyone should have four WO in a month.
Publish by printing (the sheet is set to landscape, one page wide) or by exporting a PDF to your team group. Changes after publishing should be made on the sheet, not just agreed verbally, so the roster and reality stay the same. Once the month is over, the roster becomes the reference for checking attendance and for shift allowances.
- Use Data Validation (List) on the day cells with the source pointing at the Codes sheet
- Freeze panes at column C and row 5 so names and dates stay visible while scrolling
- Save each month as a separate file; rosters are asked for in disputes about night allowance and weekly offs
How to customise codes, timings and coverage
Add codes as needed: HD for half day, T for training, OD for outdoor duty, CO for comp-off. Each new code that counts as work should be added to Total Duties; each new code that is time off should not. If some roles need cover on weekly offs, add a separate Required row per role instead of per shift.
For split shifts (common in restaurants: 11-15 and 19-23) create a code such as S with its two timings on the Codes sheet. For a department that runs two rotating teams, put the team in the Role column and sort by it. For a proper fixed rotation of three or four teams, the 3-shift schedule template is a better fit because it generates the pattern for you.
Hospitals and nursing homes usually need ward-wise coverage with a minimum of one senior nurse per shift; the nurse duty roster format adds those checks. For hotels the hotel staff duty roster format handles the department split.
- Add a Night Allowance column with =N Count x rate if your policy pays a per-night amount
- Add a Hours column with =M*8+E*8+N*8+G*9 (adjust to your timings) to check weekly hours
- Change the coverage COUNTIF ranges when you add or remove employee rows
Rules and compliance context
A duty roster is where working-hours law is applied in practice. Under the Factories Act and most state Shops and Establishments Acts, an adult worker may not be required to work more than 9 hours a day or 48 a week, with a spread-over limit (10.5 hours in factories) and at least 30 minutes' rest after 5 hours. The Labour Codes keep the 48-hour week and leave daily hours to be notified by the appropriate government. Your roster should keep each employee within 6 duties a week; the Hours column above makes that visible.
Weekly off is a statutory entitlement, not a favour. An employee rostered for 7 consecutive days needs a substituted off within the rules of your state Act. The WO Count column is your check that every employee has 4 offs in the month. See weekly off rules as per labour law for how substitution and compensatory offs work.
Night shift work is often subject to additional conditions for women employees under state Shops and Establishments Acts (consent, transport, security), and to a night shift allowance under company policy or settlement. The N Count column supports the allowance calculation; the night shift allowance policy article covers how companies structure it.
When the Excel roster stops working
The Excel roster breaks down when changes are frequent. A swap agreed between two employees on the floor, a sick call at 06:00 covered by whoever answers the phone, a new joiner added mid-month: each of these is a manual edit that somebody has to make and re-share, and the version on people's phones is soon wrong. The roster also has no idea whether the person marked M actually came in.
Attend Mitra's roster builder uses the same employee-by-day grid with shift templates (morning, evening, night, general, split, shifts crossing midnight), lets you build a draft and publish it to the team's phones, supports shift swaps with manager approval and open shifts that staff can pick up, and warns when someone with approved leave is rostered. Attendance marked in the app is compared with the rostered shift, so late marks, no-shows and night duties are counted automatically for payroll. The employee roster management software page shows the builder; the shift management feature page covers the shift rules; and the duty roster glossary entry defines the terms.