How to Make a Work Schedule in Excel: Free Template and Formulas
Build the schedule once, share it with everyone. Who’s working, who’s out and who swapped, all in one view.
14 days free. No credit card needed. Cancel anytime.

Quick Answer
How to make a work schedule in Excel: put the week's start date in one cell and build a date row from it, list employees down the side and enter shift codes from a drop-down list. A shift table turns codes into paid hours with =MOD(End-Start,1)*24, =SUMPRODUCT(SUMIF(...)) totals each employee's week, =MAX(0,hours-40) flags overtime and =COUNTIF counts coverage for every shift. For repeating rotations, =CHOOSE(MOD(date-start,cycle)+1,...) fills the whole schedule from one formula.
How to make a work schedule in Excel is easier to learn with a working example in front of you, so start with the free tool below. Copy a ready-made weekly schedule with every formula already built in, or use the rotation formula builder to create a one-cell formula that repeats any shift pattern across as many weeks as you need. Then follow the step-by-step guide to understand each formula, customize the template for your team and add drop-down lists, overtime checks, coverage counts and color coding.
Excel is still one of the best places to build an employee schedule. It is flexible, almost everyone already has it, and a well-built schedule sheet does far more than list names and shifts: it calculates paid hours, flags overtime before it happens and shows at a glance whether every shift is covered. This guide uses core Excel functions, the kind I have been teaching for years, and applies them to the job of scheduling people. Everything here also works in Google Sheets.
Free Excel Schedule Template and Rotation Formula Builder
Part 1 copies a complete weekly schedule. Click "Copy Schedule Template," click cell A1 in a blank sheet and paste. Part 2 builds a formula for rotating schedules such as Pitman, DuPont, 4-on 4-off and Continental. The default date cell, B$2, matches the template, so you can paste the formula straight into cell B4 and fill it to the right.
Excel Schedule Template and Rotation Formula Builder
Copy a working weekly schedule with formulas, or build a one-cell formula that repeats any shift rotation. Paste into Excel or Google Sheets.
Part 2: Rotation Formula Builder
Your Formula
Preview: First 14 Days
| Date | Day | Formula Result |
|---|
Planning tool only. The template and formulas are free tools for building schedules in a spreadsheet. They are not legal or payroll advice. Overtime, break and predictive scheduling rules vary by state and city, and the overtime formula in the template assumes a 40-hour workweek that matches the dates in row 2. Check your results with your payroll provider before you pay employees.
What You Need Before You Open Excel
A schedule is only as good as the information behind it. Before you build anything, collect four things: the hours you need covered each day, how many people each shift needs, each employee's availability and approved time off, and the shifts you actually use, with start times, end times and unpaid breaks. If you are not sure how many people each shift needs, start with the planning steps in how to make a work schedule for employees, then come back to build it in Excel.
It also helps to decide on shift codes. Short codes like D for day, N for night, M for morning, S for swing and O for off are faster to type and easier to count with formulas than full shift times, and a lookup table turns each code into hours.
Step 1: Set Up the Dates and Day Names
Put the first day of the week in cell B1. You can type a date, or let Excel calculate the Monday of the current week with this formula:
Formula
B1: =TODAY()-WEEKDAY(TODAY(),3)
WEEKDAY with a return type of 3 counts Monday as 0 and Sunday as 6, so subtracting it from today always lands on Monday. If your workweek starts on Sunday, use =TODAY()-WEEKDAY(TODAY())+1 instead. Then build the date row: B2 is =B1, C2 is =B2+1, and so on through H2. Below the dates, use =TEXT(B2,"ddd") in B3 to show the day name, and fill it right. Because every date is calculated from B1, changing that one cell rolls the whole schedule to a new week.
If the dates show up as numbers, select row 2 and apply a short date format. Excel stores dates as numbers, which is exactly what makes date math like =B2+1 work.
Step 2: Build a Shift Code Table
Off to the side of the schedule, build a small table with one row per shift: the code, start time, end time, unpaid break in minutes and paid hours. In the template, this table starts in L2. The paid hours column uses one formula for every shift, including overnight shifts:
Formula
Paid hours: =MOD(N3-M3,1)*24-O3/60
Here is why it works. Excel stores times as fractions of a day, so 7:00 PM minus 7:00 AM is 0.5. For an overnight shift, 7:00 AM minus 7:00 PM is negative 0.5, and MOD(...,1) wraps it back to 0.5. Multiplying by 24 turns the fraction into hours, and dividing the break minutes by 60 turns them into hours to subtract. A 7:00 AM to 7:00 PM shift with a 30-minute unpaid meal break is 11.5 paid hours. For a deeper look at converting time to decimal hours, see how to convert hours and minutes to decimal in Excel.
Only subtract unpaid meal periods. Under federal rules, short rest breaks that an employer offers, usually 5 to 20 minutes, count as paid work time, so they should not come off the paid hours.
Step 3: Add Drop-Down Lists for Shift Codes
Drop-down lists keep everyone using the same codes, which matters because every formula downstream counts those codes. Select the schedule cells, B4 through H9 in the template, then go to Data, Data Validation, choose List and set the source to =$L$3:$L$7. In Google Sheets, go to Data, Data validation, add a rule and choose "Dropdown (from a range)" with the same range.
If you add new shifts later, insert them inside the shift table range, or turn the shift table into an Excel Table with Insert, Table so the range grows automatically when you add rows.
Step 4: Calculate Hours and Overtime for Each Employee
Each employee's weekly hours come from looking up every code in their row and adding the paid hours. One formula does that without any helper columns:
Formulas
Weekly hours (I4): =SUMPRODUCT(SUMIF($L$3:$L$7,B4:H4,$P$3:$P$7))
Overtime (J4): =MAX(0,I4-40)
Microsoft 365 alternative: =SUM(XLOOKUP(B4:H4,$L$3:$L$7,$P$3:$P$7,0))
SUMIF normally takes one criterion, but when you give it a range of criteria, B4 through H4, it returns the paid hours for each day, and SUMPRODUCT adds them up. It works in every version of Excel and in Google Sheets. The overtime formula subtracts 40 and uses MAX so the result never goes below zero.
Two rules keep the overtime column honest. First, the week in row 2 must match your payroll workweek, because federal overtime for non-exempt employees is calculated on hours over 40 in each workweek. Second, never add two weeks together and compare to 80; each workweek stands on its own, so a 50-hour week owes 10 hours of overtime even if the next week is 30 hours.
Step 5: Check Coverage Every Day
A schedule can look full and still leave a shift empty. Add a coverage row under the schedule for each shift you need to staff:
Formulas
Day shift on duty (B11): =COUNTIF(B$4:B$9,"D")
Night shift on duty (B12): =COUNTIF(B$4:B$9,"N")
Total paid hours for the day (B13): =SUMPRODUCT(SUMIF($L$3:$L$7,B4:B9,$P$3:$P$7))
Coverage by role: =COUNTIFS($K$4:$K$9,"Cook",B$4:B$9,"D")
Fill the coverage formulas across all seven days. The dollar signs lock the rows so the formula keeps counting rows 4 through 9 as it moves right. If you need coverage by role, add a role column next to the employee names and switch to COUNTIFS, which counts only employees who match both the role and the shift. The daily total paid hours row is useful for comparing labor to sales; see how to calculate labor cost percentage.
Step 6: Add Conditional Formatting
Color turns a grid of codes into a schedule you can read in two seconds. Select the schedule cells, go to Home, Conditional Formatting, New Rule and choose "Use a formula to determine which cells to format." These rules cover the most useful cases:
| What to highlight | Apply to | Formula |
|---|---|---|
| Night shifts | B4:H9 | =B4="N" |
| Days off | B4:H9 | =B4="O" |
| Weekends | B2:H13 | =WEEKDAY(B$2,2)>5 |
| Overtime | J4:J9 | =J4>0 |
| Shift not covered | B11:H12 | =B11<1 |
| Over 5 shifts in a week | A4:A9 | =COUNTIF($B4:$H4,"O")<2 |
Write each formula for the top-left cell of the range, and use dollar signs only where you want a row or column to stay fixed. For more ideas, see mastering Excel conditional formatting.
Step 7: Make a Rotating Schedule With One Formula
If your team works a repeating pattern, such as a Pitman, DuPont or 4-on 4-off rotation, you do not need to type every shift. One formula can look at the date in row 2, figure out where that date falls in the cycle and return the right shift for each crew. This is the formula the builder above creates for you:
Formula pattern
=CHOOSE(MOD(B$2-start_date,cycle_length)+1,"shift on day 1","shift on day 2", ...)
Example for a 14-day Pitman day crew, with the rotation start date in $B$1:
=CHOOSE(MOD(B$2-$B$1,14)+1,"Day","Day","Off","Off","Day","Day","Day","Off","Off","Day","Day","Off","Off","Off")
B$2 minus the start date is the number of days since the rotation began. MOD divides that by the cycle length and keeps the remainder, which is the position in the cycle, from 0 to 13 for a 14-day cycle. Adding 1 turns it into 1 to 14, and CHOOSE returns the matching item from the list. Fill the formula right across as many weeks as you like and the pattern repeats perfectly, even for dates before the start date. For the other crews, use the builder to generate their formulas, or keep the same list and change the start date by the crew's offset.
Not sure which rotation to use? The rotating shift schedule guide compares the common patterns, and the Pitman schedule and 12-hour shift schedule guides each include a free generator that shows every crew's calendar and weekly hours before you build it in Excel.
Step 8: Print, Protect and Share
Freeze the headers. Click cell B4, then View, Freeze Panes, so names and dates stay visible as you scroll.
Set up printing. On the Page Layout tab, choose Landscape, set Width to 1 page under Scale to Fit and set the print area to the schedule and coverage rows. Add the week to the header so printed copies are never confused.
Protect the formulas. Select the schedule cells where managers type codes, open Format Cells, Protection and uncheck Locked. Then go to Review, Protect Sheet. Everyone can still change shifts, but nobody can accidentally overwrite a formula.
Share it. Save to OneDrive or SharePoint and share a view-only link, upload it to Google Sheets, or save the week as a PDF and post it. Whatever you choose, publish in one place so there is only one current version.
Excel Scheduling Formula Cheat Sheet
| Task | Formula |
|---|---|
| Monday of this week | =TODAY()-WEEKDAY(TODAY(),3) |
| Next day | =B2+1 |
| Day name | =TEXT(B2,"ddd") |
| Shift hours, including overnight | =MOD(End-Start,1)*24-BreakMinutes/60 |
| Weekly hours from shift codes | =SUMPRODUCT(SUMIF(Codes,B4:H4,Hours)) |
| Weekly overtime | =MAX(0,Hours-40) |
| People on a shift | =COUNTIF(B$4:B$9,"D") |
| People on a shift by role | =COUNTIFS(Roles,"Cook",B$4:B$9,"D") |
| Days off in a week | =COUNTIF(B4:H4,"O") |
| Weekend check | =WEEKDAY(B$2,2)>5 |
| Repeating rotation | =CHOOSE(MOD(B$2-Start,Cycle)+1,...) |
For more formulas, keep the Excel formulas cheat sheet handy.
Excel vs. Google Sheets for Scheduling
| Excel | Google Sheets | |
|---|---|---|
| Formulas in this guide | All work | All work |
| Drop-down lists | Data, Data Validation, List | Data, Data validation, Dropdown (from a range) |
| XLOOKUP | Microsoft 365 and newer versions | Available |
| Sharing | OneDrive or SharePoint link | Built-in sharing and live editing |
| Printing | More control over page layout | Simpler, fewer options |
| Offline use | Full desktop app | Offline mode in some browsers |
If you work in Google Sheets, the template and every formula paste in the same way. For ready-made sheets, see the free schedule templates for Google Sheets and Excel.
Common Excel Schedule Mistakes
- Typing times as text. "7am" is text to Excel and breaks the math. Type 7:00 AM so Excel stores a real time.
- Leaving out MOD on overnight shifts. End minus start goes negative when a shift crosses midnight. MOD fixes it.
- Hours formatted as time. If a total shows 9:30 instead of 57.5, the cell has a time format. Multiply by 24 and format as a number.
- Hard-coding hours. Typing 12 into a cell instead of using the shift table means every change has to be made twice.
- Mismatched workweeks. If the schedule week does not match the payroll week, the overtime column is wrong.
- Inserting rows outside the ranges. New employees added below the last row are left out of COUNTIF and SUMIF ranges. Insert rows inside the range, or use Excel Tables.
- Subtracting paid breaks. Only unpaid meal periods should come off paid hours.
Example: A Small Clinic Builds Its Schedule
A family clinic with eight staff kept its schedule on a whiteboard, which meant nobody could see next week from home and overtime surprises showed up on payroll. The office manager rebuilt it in Excel using the steps above: a shift code table with the clinic's three shifts, drop-down lists for each day, weekly hours and overtime for each employee and a coverage row that turned red when the front desk had fewer than two people.
The first week, the coverage row caught a Thursday afternoon with only one front desk person before the schedule was posted, and the overtime column showed a medical assistant heading for 46 hours. The manager adjusted both before publishing. She protected the formula cells, saved the file to OneDrive and shared a view-only link, so staff could check the schedule on their phones. Each new week now takes about fifteen minutes: change the date in B1, update the codes and check the red cells.
When a Spreadsheet Is Not Enough Anymore
A well-built Excel schedule can run a small team for years. It is worth upgrading when the schedule starts needing things a spreadsheet was not designed to do: employees requesting swaps and time off from their phones, approvals that leave a record, schedules for several locations, comparing scheduled hours with actual clock-ins or sending hours straight to payroll. At that point the formulas are still right, but the manual work around them grows every week.
Final Thoughts
Making a work schedule in Excel comes down to a handful of formulas: dates that roll forward from one cell, a shift table that turns codes into paid hours, SUMPRODUCT and SUMIF for weekly totals, MAX for overtime, COUNTIF for coverage and CHOOSE with MOD for rotations that repeat on their own. Add drop-down lists and conditional formatting and the sheet starts catching problems before you publish. Copy the template above to start with all of it already built, then customize the shifts and names for your team.
Take Your Schedule Beyond the Spreadsheet With Updoot
When your team outgrows the spreadsheet, Updoot's scheduling software picks up where Excel leaves off. You can copy last week's schedule, assign rotating shift patterns, see approved PTO and availability while you build and spot overtime before you publish. Employees get the schedule on their phones, request swaps in the app and clock in to scheduled shifts with the built-in employee time clock, so you can compare scheduled and actual hours every week. And because every Updoot tool exports to Excel or Google Sheets, your data is always yours to analyze with the formulas you already know.
See the Updoot pricing page for current plans, and start free with your team.
Sources
- U.S. DOL Fact Sheet #23: Overtime Pay Requirements of the FLSA
- 29 CFR 778.105: Determining the Workweek
- U.S. Department of Labor: Breaks and Meal Periods
Related Reading
4 Free Schedule Templates for Google Sheets and Excel →
