To make a work schedule in Excel, put one person per row and two columns per day, an in time and an out time. Turn each day into hours with =MOD(end-start,1)*24, add the seven days together in one weekly total column, take the unpaid breaks off, then use conditional formatting to turn that total red above 40. Eight steps, every formula written out, and one worked week you can copy.
What you need before you open Excel
Learning how to make a work schedule in Excel starts before you open Excel, because the sheet is only as good as what goes into it. Five things decide whether it works or gets rebuilt on Thursday. Write them down first.
Everybody who can work
Names, and the job each does. Two jobs, write both.
The shifts you actually run
Start time, end time, unpaid break.
How many people each shift needs
One on the open, one on the close is a real answer.
The days each person cannot work
The Tuesday class, the second job, the standing appointment.
Time off you have already said yes to
Dates, not months. An approved Thursday is the one that bites.
For example, let’s say you run a coffee shop. It opens seven days and you have six people: Rosa, Ben, Kayla, Omar, Nina and Grant. There are two shifts a day, an open from 6:00 AM to 3:00 PM and a close from 10:00 AM to 7:00 PM, each carrying a 30-minute unpaid break, and you need one person on each. That team runs every step below. The layout is the same for a store, a clinic or a site.
Step 1.Set up the columns for your Excel work schedule
The layout is the whole job. Get it wrong and no formula on this page works, so copy this part exactly: person in column A, what they do in column B, then two columns for every day of the week, an in time and an out time. That is fourteen columns, C through P, then Q holds the weekly hours, R the pay rate, and S what that person costs.
| A | B | C | D | E | F | … | O | P | Q | R | S | |
| 1 | 9/21/2026 | 9/22/2026 | … | 9/27/2026 | ||||||||
| 2 | Name | Role | Mon in | Mon out | Tue in | Tue out | … | Sun in | Sun out | Hours | Rate | Cost |
| 3 | Rosa | Shift lead | 6:00 AM | 3:00 PM | 6:00 AM | 3:00 PM | … | 42.50 | 18 | 787.50 | ||
There is a prettier way to do this. Put the shifts down the side, type names into the day cells, and the sheet reads like a wall calendar. It also cannot add a single person’s week up, because a name in a cell is text and text has no hours in it. I would never build that version. One person per row is the difference between a picture of a week and an employee schedule Excel can actually total.
Prefer to start from something already built? Download the free employee schedule template and rename it. It is a work schedule template Excel and Google Sheets both open. The same layout is how to make a staff schedule in Excel for thirty people. You add rows, never columns.
Step 2.Put the week’s dates across the top
Typing seven dates by hand is how a schedule ends up with two Wednesdays. Type one date. Let Excel work the rest out, so next week is a single edit instead of seven.
C1, typed by hand, then E1 dragged right
9/21/2026 then in E1: =C1+1
Each day sits above two columns. The next date is therefore two columns along, which is why E1 points back at C1 and not at D1. Drag E1 right across G1, I1, K1, M1 and O1, and the week fills itself in. For the day name, put this in row 2 and drag it the same way.
The short day name from a date
=TEXT(C1,"ddd")
TEXT takes a date and a format code, so change "ddd" to "dddd" for Monday instead of Mon. And next week is now one cell: change C1 to the following Monday, and every date and every day name across the whole sheet moves with it.
Step 3.Type start and end times so Excel can do math with them
Excel does not store a time the way you read it. Behind 6:00 AM is 0.25, because six in the morning is a quarter of the way through the day. Noon is 0.5. That one fact makes every formula below work, and it is also what breaks the whole sheet when Excel decides your time is text.
Excel sees text
- 6am
- 6.00
- 6:00AM
- 6 to 3
Text sits on the left of the cell. Any formula reading it returns a #VALUE! error or a silent zero.
Excel sees a time
- 6:00 AM
- 06:00
- 18:00
- 3:00 PM
A real time sits on the right of the cell. That is the only check you need.
A time on the left means the column is formatted as Text, and retyping will not fix it. Select the day columns, set the format to Time, then retype one cell and watch it jump right. I do this before I fill in a single day, because a sheet with fourteen columns of text looks completely normal on screen and every total on it quietly reads 0.00.
Step 4.Turn a start time and an end time into hours
One shift at a time first. The weekly formula is just this one, repeated seven times. You build it in three moves. Each one fixes a problem that the move before it quietly leaves behind for you. Do them in order.
=D3-C3
Subtract the in time from the out time. Correct arithmetic, unreadable answer: Excel returns 0.375 and shows it as 9:00 AM.
=(D3-C3)*24
Multiply by the 24 hours in a day and 0.375 becomes 9. Format the cell as Number with two decimals so it reads 9.00.
=MOD(D3-C3,1)*24
MOD handles an overnight shift. 10:00 PM to 6:00 AM subtracts to a negative number and MOD turns it back into 8. Use this version always.
MOD returns the remainder after a division, which is why a negative overnight result comes back positive. Now take the break off: keep it in minutes so you can still read it, divide by 60 to turn those minutes into a share of an hour, and a 30-minute unpaid break becomes 30/60, which is 0.5 of an hour.
One shift, overnight-safe, with a 30-minute unpaid break
=MOD(D3-C3,1)*24-30/60
For example, Rosa’s Monday runs 6:00 AM to 3:00 PM. The subtraction gives 0.375. Times 24 is 9 hours, and minus the half-hour break is 8.50 paid hours. That is the number that has to reach the weekly total, and it is the reason a nine-hour shift is never nine hours when payroll runs.
Step 5.Total each person’s week in one cell
Seven days go into one cell. Add the seven MODs inside one set of brackets, multiply the lot by 24 once at the end, then take the breaks off by counting the days that person actually worked. A blank day contributes nothing, because MOD of nothing minus nothing is zero.
Column Q, row 3, then copied down every row
=(MOD(D3-C3,1)+MOD(F3-E3,1)+MOD(H3-G3,1)+MOD(J3-I3,1)+MOD(L3-K3,1)+MOD(N3-M3,1)+MOD(P3-O3,1))*24-COUNT(C3,E3,G3,I3,K3,M3,O3)*0.5
The COUNT at the end does the clever part. It counts only the in-time cells holding a real number, which is the same as counting the days that person worked, then multiplies by the 0.5 hours of break. Five worked days takes 2.5 hours off. One takes half an hour off. Change 0.5 to 0.25 for a fifteen-minute break, or delete it if your breaks are paid.
Here is the worked week. Select Q3, copy it, paste it down Q4 to Q8, and Excel moves every row reference for you.
| Name | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Q: Hours |
| Rosa | 6a to 3p | 6a to 3p | 10a to 7p | 6a to 3p | 6a to 3p | · | · | 42.50 |
| Ben | · | · | 6a to 3p | · | 10a to 7p | 6a to 3p | · | 25.50 |
| Kayla | · | 10a to 7p | · | · | · | · | 6a to 3p | 17.00 |
| Omar | 10a to 7p | · | · | · | · | 10a to 7p | · | 17.00 |
| Nina | · | · | · | 10a to 7p | · | · | · | 8.50 |
| Grant | · | · | · | · | · | · | 10a to 7p | 8.50 |
| Total hours scheduled | 119.00 | |||||||
Look at Rosa. Five ordinary nine-hour days, nothing unusual anywhere, and she finishes on 42.50 hours. Nobody gave her a double and nobody worked a seventh day. That is the number a schedule hides in plain sight, and the next step makes it impossible to miss.
Step 6.Flag anyone over 40 hours automatically
Conditional formatting is an Excel rule that recolours a cell on its own when the number passes a line you set. Set the line once at 40. Every weekly total polices itself from then on.
Where to click
- Select Q3:Q8, the weekly totals and nothing else.
- On the Home ribbon, open Conditional Formatting.
- Choose Highlight Cells Rules, then Greater Than.
- Type 40 in the box.
- Pick Light Red Fill with Dark Red Text and press OK.
Rosa’s 42.50 turns red the moment the rule lands. Everybody else stays plain. One glance down column Q now tells you who is over 40. You do not read a number. You read a colour.
Step 7.Colour the whole row, and catch the shift with no end time
To colour the whole row, use New Rule, then Use a formula to determine which cells to format. Then type this. The dollar sign locks the column so every cell in the row looks at Q, and the row number stays loose so the rule moves down with it.
New Rule, applied to A3:S8, to colour the whole row
=$Q3>40
Two more rules while you are in here. I set my own warning at 38 rather than 40, in a softer amber, because by the time a cell reads 41 the shift is already promised to somebody. The second is a Greater Than rule at 12 on a single day. It catches the typo where an out time never got entered, because an empty out cell makes MOD read the shift as running to midnight, and the day silently balloons.
Step 8.Add pay and see what the week costs
Hours become money at two prices. A schedule that only counts hours hides the expensive half of the bill. Every hour up to 40 costs the person’s rate. Every hour past 40 is paid at time and a half. So an $18 hour becomes $27, and $9 of every one of those hours buys you nothing at all.
Rates go in column R. One formula in column S then splits the week at 40 and prices each half, because MIN takes whichever is smaller, the hours or 40, and MAX takes whatever is left, or zero.
Column S, row 3, then copied down
=MIN(Q3,40)*R3+MAX(Q3-40,0)*R3*1.5
For example, take Rosa at 42.50 hours on $18. MIN gives 40 hours at $18, which is $720. MAX gives 2.5 hours at $27, which is $67.50. So her week costs $787.50, and $22.50 of that is pure premium on two and a half hours that nobody planned or asked for. Total the column with =SUM(S3:S8): 119 × $18 = $2,142, plus Rosa’s $22.50 of premium, which is $2,164.50 for the week.
Rosa’s first 40 hours
Rosa’s last 2.5 hours
$22.50 in one week at one coffee shop is a rounding error, and that is exactly why nobody ever catches it. Now scale it. Run the same week across a 40-person operation where ten people average five hours over. That is 10 × 5 = 50 overtime hours, at $9 of premium each, so 50 × $9 = $450 a week and $450 × 52 = $23,400 a year. Five locations makes it five times that, $117,000, every dollar committed on a Tuesday afternoon by somebody typing into a spreadsheet.
Step 9.Check the schedule before anybody sees it
The sheet now adds up. What it cannot tell you is whether the week is right, so the last pass is yours. Run these five checks before anybody sees it.
Five checks before you send it
Every shift has somebody on it. Read down each day pair for a gap.
Nobody is on two shifts at once. Two in-times on one day is a double-booking.
Nobody is working a day they told you they cannot. Skip this one and it costs you a morning.
Nobody is scheduled over time off you already approved.
Column Q has no red in it, or you know exactly why it does.
Step 10.Print it and send it
To print it, go to Page Layout, set Orientation to Landscape and Scaling to Fit Sheet on One Page, then use Print Titles to repeat row 2 on every page. I never send the spreadsheet itself. Save it as a PDF, because a PDF cannot be quietly edited by whoever opens it. Then send it once, and expect to send it again.
That is how to build a work schedule in Excel from an empty sheet, end to end. Everything below is what happens to that sheet once a real week starts moving, and the people in it start changing their minds about which days they can work.
The better solution once the sheet stops working
Everything above is how to create a work schedule in Excel and keep the arithmetic honest. Excel is a calculator with a grid on it. What it has no idea about is people: who is free, who asked for Thursday off, who just dropped a shift, or who is still looking at last Sunday’s version on their phone. Every one of those gaps is filled by you, by hand, after the schedule is already built.
Six jobs a work schedule has, and what Excel does with them
6 gapsKnow who cannot work Tuesday
Nothing in the sheet holds anybody’s availability. Tuesday looks exactly like Monday.
You publish it wrong, find out on the day, and spend 20 minutes fixing a hole you made. Twice a week is 34 hours a year.
Know who already has time off approved
The approval lives in a text message. The sheet never saw it, so it lets you type Nina over the Thursday she booked off.
The day runs one person short, open to close. Nine hours of your own floor time.
Stop somebody crossing 40 hours
The over-40 rule fires after the shift is typed in. Excel colours the cell, it does not refuse it.
Five ordinary nine-hour days is 42.5 hours. Rosa’s 2.5 hours past 40 cost $27 instead of $18, so $22.50 buys nothing.
Cover a shift somebody drops
There is no list of who is allowed to take it. You work from memory.
20 minutes of calls per call-off, and the first yes is usually the person closest to 40.
Keep a shift swap honest
Two people agree a trade, you change two cells, and nothing recounts either week.
The trade puts one of them at 46 hours. Six hours at $27 instead of $18 is $54 of premium on a deal you were never part of.
Tell the team it changed
Excel has no idea anybody is waiting on it. You edit the cell, and the version your team photographed on Sunday still says the old time.
You send the same week three times before Wednesday. With the calls, the call-offs and the swaps: about 3 hours a week, or 156 hours a year.
The last one is bigger than it looks. The Shift Project at Harvard Kennedy School surveys hourly workers directly, and found 60 percent get less than two weeks’ notice of their schedule, and 57 percent have the timing of a shift changed at the last minute (The Shift Project). Two thirds would rather have a predictable one.
The same group, summarising Choper, Schneider and Harknett in the ILR Review, reports that schedule instability pushes people out of the job, mostly through how satisfied they are and how much it collides with their own family. People leave. I would not run my week off a photo on somebody’s phone. That is what a resent spreadsheet becomes. And every replacement hire is one more row you have to teach it about.
Past a certain point you use an AI scheduling tool
The fix is not a spreadsheet. It is employee scheduling software that holds the facts a spreadsheet has nowhere to put, and checks them every time a shift is placed. The AI Copilot builds the schedule from a typed sentence, and Autopilot holds the rules you write.
You type “Generate this week’s schedule” and the AI Copilot fills the shifts you already have. Before it puts anybody on one, it runs the Step 9 checks you were doing by eye, plus every rule you wrote in your own words:
Checked on every single shift
18 checksWorks at this location
Approved time off
Days they can never work
Hours they cannot work
Already on an overlapping shift
Has the role the shift needs
Weekly hours cap
Weekly shift cap
Shift length cap
Would cross 40 hours
Weekly labor budget
Daily labor budget
Rest between two shifts
Days in a row
Two weekends back to back
Two people kept apart
Time off notice you require
How many people are already off
The difference from the red chart is where the check happens. A spreadsheet colours a cell after the shift is typed in, when the only thing left to decide is how to unpick it. An Autopilot rule refuses the shift while somebody is still saving it, when changing it costs nothing. The same rules run when the AI Copilot builds the week, when a manager saves a shift by hand, and when a dropped shift is covered.
Which covers the four jobs the spreadsheet left on your desk. Employee availability and approved time off live on the person, so no formula has to remember them. When somebody gives up a shift and call-off coverage is switched on, Autopilot works that list in order and assigns or offers the shift. A swap can wait. And whoever a manager assigns gets an email. More on that in five signs it is time to ditch your scheduling spreadsheet.
A work schedule in Excel against scheduling software, job by job
Everything above is how to make a schedule for employees on Excel, for nothing. Here are the same nine jobs in both columns, so you can see exactly where the sheet stops being enough for the week you actually run.
| The job | A work schedule in Excel | XShift AI |
|---|---|---|
| Building next week | Every shift typed by hand | One sentence fills the open shifts |
| Hours per person | A formula, once you write it | Counted before a shift can save |
| Anybody over 40 | The cell goes red afterwards | The shift is refused first |
| Who is free | Lives in your head | Held on the person, checked every time |
| Approved time off | Lives in your inbox | Checked on every shift |
| Somebody drops a shift | You start texting | The qualified list is worked in order |
| Telling the team | You resend the file | The person assigned gets an email |
| Labor cost of the week | A formula you wrote | Priced per shift, blocked over budget |
| What it costs | Free | $29 a month plus $1 per active user |
Build next week free, then download it to Excel
To see a filled week before you change anything, the free employee shift schedule generator takes a few typed lines and no account. Say who you have, your hours, how many people you need on and a rule like “nobody over 40 hours”, then press Build.
It fills the week, marks any slot it could not fill with the reason, and downloads a spreadsheet that opens straight in Excel with everybody’s hours totalled. No email, no card, and it prints landscape on one page. You can also feed it the sheet you just built: save it as a CSV, or photograph it, and drop the file in. Up to 20 people, one location, one week.
Build a work schedule for employees free, no sign-up
The same employee schedule in Google Sheets
Every formula above works in Google Sheets without a change. MOD, COUNT, TEXT, MIN, MAX and SUM behave identically, and only conditional formatting moves, to the Format menu. The real advantage is the link: a Sheet has a live address, so your team opens the current week instead of a photo of Sunday’s. To build it there, follow how to make an employee schedule in Google Sheets step by step.
What it costs to stop building the schedule by hand
XShift AI is $29 a month plus $1 per active user after the first, after the free trial of 21 days. Every manager counts as a user.
The six-person coffee shop plus one manager is 7 users: $29 + (6 × $1) = $35 a month
That week cost $2,164.50 in wages. $35 a month is about $8 a week, 0.4 percent of it.
A 40-person operation with 4 managers is 44 users: $29 + (43 × $1) = $72 a month
Against the $23,400 a year that example leaks in premium, $864 a year is about 27 to 1.
Picture next Sunday without the spreadsheet. The week is built, nobody is over 40, the person who dropped Thursday has already been replaced, and all you did was read it. Get fully set up in minutes by pasting your staff list, or a photo of it, into the AI Copilot, and keep the spreadsheet running alongside it.
Stop building next week by hand
Frequently asked questions
How do I make a work schedule in Excel that updates automatically?
In Excel the numbers update automatically and the schedule does not. Once the hours formula and the weekly total are in, every figure and the red over-40 flag recalculate the second you change a time. Excel will never fill an empty shift, recheck who is free, or tell your team it changed. XShift AI is the scheduling software that updates automatically: the AI Copilot fills your open shifts from one typed sentence, Autopilot covers a dropped shift once call-off coverage is on, and whoever a manager assigns gets an email.
How do I make a work schedule?
Start from five things: everyone who can work, the shifts you actually run, how many people each shift needs, the days each person cannot work, and any time off you have approved. Then place people shift by shift, add up each person’s week as you go, and catch anybody crossing 40 hours before you publish. In a spreadsheet that means one person per row, two columns per day and a weekly total formula. XShift AI does the same job from one typed sentence: the AI Copilot fills your open shifts against location, approved time off, unavailable days, blocked hours, overlapping shifts and role, and Autopilot blocks any shift that breaks a rule you wrote.
How do I calculate hours worked in Excel?
The Excel formula for hours worked is =MOD(D3-C3,1)*24. Subtract the start time from the end time, wrap it in MOD so an overnight shift still works, and multiply by 24. Take an unpaid break off in minutes, so 30 minutes is -30/60. Format the answer as Number with two decimals, because Excel copies the time format from the cells you subtracted and shows 9:00 AM instead of 9.00.
How do I make a monthly work schedule in Excel?
Build one week, then copy the block down four or five times and change the dates. Give every block its own weekly total, so nobody’s month is added up as one 170-hour number. Overtime is counted by the week, so a monthly work schedule in Excel needs the over-40 rule on each week separately. In XShift AI a cycle can be a week, two weeks, a month or three months, and the overtime rule still counts each week on its own.
How do I make a work schedule calendar in Excel?
Turn the layout on its side. Put the seven dates across row 1, use one row per shift instead of one per person, and type names into the day cells. A work schedule calendar in Excel prints beautifully and anybody can read it at a glance. It cannot add a single week up, because a name in a cell is text. Most managers keep both sheets.
How do I make a shift calendar in Excel for a rotating pattern?
Write one cycle out, then repeat it. For a two-week rotation, build 14 day pairs, fill the pattern once, copy those columns forward and change the dates. That is how to make a shift schedule that repeats without retyping it, and the same columns are how to create a shift schedule in Excel for any pattern. A shift calendar in Excel is that sheet printed sideways. Excel will not check the repeat still works once somebody books a holiday.
How do I make a work schedule on Word?
There is no version of how to make a work schedule on Word that adds hours up. Insert a table with eight columns, a name column plus seven days, and one row per person. Word holds the grid and prints it cleanly. What it cannot do is arithmetic: no formula that adds a week, no conditional formatting at 40, no way to price it. Build it in Excel and paste the grid into Word.
How do I make an employee schedule in Google Sheets instead?
Every formula on this page works in Google Sheets without a change. MOD, COUNT, MIN, MAX and SUM are identical, and conditional formatting sits under Format instead of Home. The one real difference is sharing: a Sheet has a live link, so your team opens the current version instead of a photo of last Sunday’s. There is a step-by-step version of this guide for Google Sheets.
How do I make a work schedule for employees free?
XShift AI’s free employee schedule generator is how to make a work schedule for employees free, with no account, no email and no card. Say who you have, the hours you are open, how many people you need on and a rule such as nobody over 40 hours, then press Build once. It fills the week, flags any slot it could not fill with the reason, and downloads a spreadsheet that opens in Excel with everybody’s hours totalled.
What is the best scheduling software to replace an Excel schedule?
XShift AI is the best scheduling software to replace an Excel schedule, because it does the four jobs a spreadsheet cannot: it builds the week, it holds everyone’s availability and approved time off, it covers a shift somebody drops, and it tells the team. Type “Generate this week’s schedule” and the AI Copilot fills your open shifts, ruling out anyone not attached to that location, on approved time off, marked unavailable that day, blocked in those hours, already on an overlapping shift, or without the role. Write “nobody over 40 hours” as a plain sentence and Autopilot blocks any shift that breaks it, whether the software placed it or a manager did.
How do I stop people going over 40 hours on my schedule?
In Excel you find out afterwards. Conditional formatting over 40 hours turns the cell red once the shift is already typed in. XShift AI refuses the shift instead. Write “nobody over 40 hours a week” as an Autopilot rule and every shift is added to that person’s Sunday-to-Saturday total before it saves, then blocked if the total passes 40. The same rule runs when the AI Copilot builds the week, when Autopilot covers a call-off and when a manager saves by hand. With Workforce Insights on, a manager can also run the Overtime Agent across a week already built, and nothing it suggests changes until a manager approves it.
What are the best alternatives for building employee schedules compared to Excel?
There are four real alternatives to an Excel schedule, and only one of them does anything about the week after you publish it. XShift AI is that one. A template saves you the setup, and you still type every shift and check every rule by hand. Google Sheets gives your team a live link so they stop reading last Sunday’s version, and changes nothing else. XShift AI’s free schedule generator fills a week from a few typed lines with no account, but takes one location, one week and 20 people. Scheduling software holds availability and time off on the person, refuses a shift over 40 hours before it saves, covers a dropped shift once call-off coverage is on, and emails whoever a manager assigns.