How to create a weekly roster in Excel

Written by Roster Flow. Published 18 June 2026.

The quickest way to a roster in Excel is a grid with names down the side and days across the top. It looks right, but it is hard to add up, because a cell that says "6:30 to 2:30" is text, not time. The layout below keeps one row for every shift, lets Excel do the arithmetic, and then builds the grid people read from those rows. It takes a little longer to set up the first time and saves every week after that.

Before you open Excel

Gather these first, because a roster built without them gets rebuilt:

• A list of the team, with each person’s role, employment type and agreed hours
• Everybody’s availability for the week, and any approved leave
• The trade you expect each day: sales by hour, bookings, deliveries and events
• The rostering rules from the award or agreement, such as minimum engagement and breaks
• Hourly rates, if you want the sheet to estimate the wage cost

Set up three tabs

Use one workbook for each week, or one workbook with a new Shifts tab each week, and set it up with three tabs. Keeping the data in one place and the display in another is what stops the sheet breaking when a shift moves.

On the Shifts tab, put the headings in row 1 and start the first shift in row 2. The formulas below assume date in column A, day in B, name in C, role in D, start in E, finish in F, unpaid break minutes in G, hours in H, label in I and cost in J.

Type times Excel can add up

Format the start and finish columns as Time, using the hh:mm format, and type times with a colon: 6:30 or 18:30. Excel stores these as fractions of a day, which is what lets it subtract one from another. Avoid typing 6.30, which Excel reads as a number, or 6:30pm without a space, which it may read as text. For the day column, =TEXT(A2,"ddd") turns the date into Mon, Tue and so on, so the day can never disagree with the date.

Work out the hours for each shift

In H2, enter this formula and fill it down the column, then format the column as a number with two decimal places:

=MOD(F2-E2,1)*24-G2/60

Finish minus start gives the length of the shift as a fraction of a day. Wrapping it in MOD(…,1) fixes the one case that catches everybody: a shift that finishes after midnight, where finish minus start would otherwise be negative. Multiplying by 24 turns the fraction into hours, and dividing the break by 60 turns minutes into hours before it comes off.

These are decimal hours, which is what payroll usually wants: 7.25 is seven hours and fifteen minutes. If you want to check a figure by hand, the work hours calculator does the same sum, and the decimal hours converter turns minutes into decimals.

Total each person’s week

On the Team tab, add a column for rostered hours and use SUMIFS to total the Shifts tab for each name. If names are in column A of the Team tab:

=SUMIFS(Shifts!H:H,Shifts!C:C,A2)

Because the total reads from the shift rows, it updates the moment a shift is added, moved or deleted. Nobody has to remember to fix a total at the end of a row.

Build the grid people read

In column I of the Shifts tab, build a label for each shift: =TEXT(E2,"h:mm")&" to "&TEXT(F2,"h:mm"). Then, on the Week tab, with names down column A and the day abbreviations Mon to Sun across row 1 from column B, this formula shows each person’s shifts for the day:

=TEXTJOIN(", ",TRUE,FILTER(Shifts!$I:$I,(Shifts!$C:$C=$A2)*(Shifts!$B:$B=B$1),""))

FILTER and TEXTJOIN are available in Microsoft 365 and Excel 2021 onwards. In older versions, build the grid by hand from the Shifts tab, or use a PivotTable with names as rows, days as columns and the sum of hours as values, which shows hours rather than times but still updates itself.

Estimate the wage cost

In column J of the Shifts tab, multiply the hours by the rate on the Team tab. In Microsoft 365, =H2*XLOOKUP(C2,Team!A:A,Team!E:E) does it; in any version, =H2*VLOOKUP(C2,Team!A:E,5,FALSE) does the same. Total column J for the week.

Treat the result as an estimate. Weekend, evening, public holiday and overtime hours are paid at different rates under most awards, and a single hourly rate per person will understate them. To see how much of the week falls on a Saturday, a Sunday or a public holiday, the weekend and public holiday hours calculator splits the hours for you, and the award pay rates pages show the rates the award sets for each.

Build the checks in

The point of a sheet is that it can warn you before the roster goes out. Add these with Conditional Formatting, using a formula rule, so problem cells turn a colour you will notice:

• Too many hours. On the Team tab, highlight a total above the person’s agreed hours or above the weekly maximum you have decided to work to. For a full-time employee, the National Employment Standards set 38 hours plus reasonable additional hours.
• Shifts too short. On the Shifts tab, highlight hours below the minimum engagement in the award, for example =H2<3 if the award you checked sets three hours. Use the figure from your own award.
• Double booking. Highlight a person rostered twice on the same date with =COUNTIFS($C:$C,$C2,$A:$A,$A2)>1, then check whether it is a genuine split shift or a mistake.
• Typos in names. Use Data Validation on the name column with a list that points at the Team tab, so a shift can only go to somebody who exists.

Share it and print it

Freeze the top row and the name column on the Week tab so they stay in view. Set a print area, switch the page to landscape and choose to fit all columns on one page, then save a PDF for the noticeboard or the group chat. Put the week and the date it was published in the top cell, so an old copy is obvious. If the workbook is shared through OneDrive or SharePoint, several people can open it at once in Excel for the web, but decide who is allowed to change it.

Where Excel starts to strain

A good sheet can run a small team for years. The strain shows up in the same places for most businesses: availability arrives by text message and has to be typed in, a change made to Tuesday’s copy never reaches Wednesday’s, a staff member works from an old PDF, and at the end of the week the hours are retyped from the sheet or from paper into payroll. None of that is a formula problem, which is why better formulas do not fix it.

If those problems sound familiar, the guide to moving off a spreadsheet covers the move. Roster Flow keeps the parts of this layout that work, one record per shift and totals that add themselves up, and adds what a sheet cannot: availability and leave from staff phones, the roster published to everybody at once, clock ins that become timesheets, and approved hours sent to Xero, MYOB or a CSV file.

A quick checklist

• Start and finish are formatted as time and typed with a colon
• Hours use MOD, so shifts past midnight add up
• Weekly totals come from the shift rows, not typed in
• Checks flag long weeks, short shifts and double bookings
• The cost is labelled as an estimate, with weekend hours checked separately
• The printed copy shows the week and the date it was published

Sources

• Fair Work Ombudsman: hours of work

Back to the blog