To create a work schedule in Excel, make a table with one row per shift, add columns for the date, employee, start time, end time, and unpaid break, then calculate shift hours and review the weekly totals. In Microsoft Excel for Microsoft 365 on Windows, this approach is easy to filter, update, and print. Before entering shifts, decide the schedule period and confirm the employees’ availability and any workplace rules you need to follow.
What You’ll Need
- Excel for Microsoft 365 on Windows. Excel for Mac and Excel for the web have the same basic worksheet features used here, although ribbon placement and some dialog layouts may differ.
- A list of employee names and the dates you need to schedule.
- Shift start and end times, unpaid break lengths, availability, and any coverage requirements you need to account for.
- A clear decision about how you will label overnight shifts and which day should receive the hours in a weekly total.
Step-by-Step
- Choose the period and rules. Decide whether you are making a weekly or longer schedule, and write down the dates it covers. Check the hours or coverage each day needs and collect employee availability before assigning shifts. If your workplace has rules about breaks, maximum hours, or rest between shifts, use the relevant workplace policy as your reference; Excel will not verify that the schedule complies automatically. For a schedule that crosses midnight, agree on whether the shift belongs to the date it starts. The example below uses the shift’s start date.
- Set up a shift-entry table. In a blank worksheet, enter these headings in row 1: Date, Employee, Start, End, Unpaid break (hours), and Hours. Put one shift on each row, even if the same person works several days. For example, a Monday shift and a Wednesday shift for one employee should be separate records. This layout makes it easier to sort, filter, check individual shifts, and calculate totals than putting several shifts in one cell. Select the headings and a few blank rows beneath them, press Ctrl+T, confirm that My table has headers is selected, and choose OK. On a Mac, use Command+T to create a table. Excel tables expand as you add records and can carry formulas and formatting into new rows.
- Format the date and time columns. Select the Date column and choose Home > Number Format, then select Short Date. Format Start and End as a time using Home > Number Format > Time. Enter times consistently, such as 9:00 AM and 5:00 PM; Excel will store them as times rather than plain text when entered in a recognizable format. In Unpaid break (hours), enter a number of hours: use 0.5 for a 30-minute break or 1 for a one-hour break. Do not type 30 to mean 30 minutes, because the calculation will treat that as 30 hours. If you prefer to record breaks as times, convert them to decimal hours before using the formula below.
- Enter each employee’s shifts. Fill in the date, employee name, start time, end time, and break for each shift. Use actual dates, not weekday names typed as text, so the schedule can be sorted and totaled by date. If a shift runs overnight, enter its actual start and end times on the same schedule row; the hours formula will account for the time crossing midnight. A start and end time that are identical calculates as zero hours, so do not use identical times to represent a full-day shift. Split unusually long shifts or handle them according to your scheduling rules.
- Calculate hours for each shift. With Start in C2, End in D2, Unpaid break (hours) in E2, and Hours in F2, enter this formula in F2:
=IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24-E2). Press Enter. The MOD calculation handles an end time after midnight, and multiplying by 24 converts Excel’s stored time fraction to hours. For a shift from 9:00 AM to 5:00 PM with a 0.5-hour unpaid break, the result is 7.5 hours. For a 9:00 PM to 5:00 AM shift with the same break, the result is also 7.5. If you created an Excel table, the formula should fill down its Hours column; otherwise, use the fill handle at the bottom-right corner of F2 to copy it down. Format Hours as Number with one or two decimal places, not as a clock time. Review break entries so they do not exceed the shift length. - Add a weekly total for each employee. Create a small summary area beside the schedule. Put employee names in H2:H20 and enter the Monday date for the week in J1. With shift dates in A2:A100, employee names in B2:B100, calculated hours in F2:F100, the employee to total in H2, and the week’s Monday date in J1, enter this formula in K2:
=SUMIFS($F$2:$F$100,$B$2:$B$100,H2,$A$2:$A$100,">="&$J$1,$A$2:$A$100,"<"&$J$1+7). It adds hours for that employee on dates from Monday through Sunday. Copy K2 down for the other names. For example, if Alex has three shifts of 7.5, 7.5, and 4 hours during that week, the total is 19 hours. Adjust the row limits if your schedule has more than 100 records. The formula assumes that overnight hours are assigned to the shift’s start date. - Make the schedule easy to use and check. Use the filter arrows in the table headings to show one employee or one date at a time. To keep the headings visible while you scroll, select View > Freeze Panes > Freeze Top Row. Apply a simple fill color to the heading row and use consistent number formats. Compare each day’s assigned shifts with the coverage you planned, then scan the weekly totals for unexpected values. If you need a paper copy, select the schedule area and use Page Layout > Print Area > Set Print Area; Landscape orientation can make a wide schedule easier to read. Before sharing, save the workbook and check that the printed or shared view does not expose information you do not intend to distribute.
Common Mistakes
A frequent problem is entering times as text. Values such as “nine to five” or an apostrophe before a time will not calculate like real Excel times. Enter recognizable time values and check that they behave as numbers when formatted as Time. Another mistake is putting multiple shifts in one cell. That makes filtering and totals unreliable; keep one shift per row.
Breaks are another source of incorrect totals. A break of 30 minutes should be entered as 0.5 when the formula expects decimal hours. Do not subtract a paid break if the Hours column is meant to show paid time, and do not subtract an unpaid break twice. Also make sure the formula has been copied to every shift row. A blank in the Hours column can make a total look too low.
Finally, do not treat a weekly total as a complete check of a schedule. A total can look reasonable even if two shifts overlap or a required day has no coverage. Check dates, assignments, and coverage separately. If you schedule overnight work, remember that the weekly formula above assigns all hours to the date the shift starts.
Tips
- Keep one master schedule and save a new copy for each scheduling period. Use a clear filename that includes the week’s start date.
- Use a consistent employee spelling throughout. A small difference, such as an extra space or a nickname, can cause a name-based total to miss a shift.
- Sort by Date and then Start time when reviewing daily coverage. Use the table’s filter and sort controls rather than moving individual cells, which can separate a shift’s details.
- Keep the summary area separate from the shift records, and label its week-start date clearly. Update that date when you reuse the template.
- Consider keeping an availability or notes column only if it helps explain assignments. Avoid putting sensitive personal details in a broadly shared schedule.
- After changing a shift, check both its calculated hours and the employee’s weekly total. Make a quick final pass for gaps, overlaps, and dates outside the intended period.
Troubleshooting
Hours show as a clock time or a decimal you did not expect: Select the Hours cells and change the number format to Number. Excel stores times as fractions of a day; the formula multiplies the difference by 24 to return hours. Confirm that the break is entered in decimal hours.
The result is blank: The formula intentionally stays blank until both Start and End have values. Check that both cells contain actual time values, not text, and that the formula is present in the row’s Hours cell. In a table, click the cell to see whether it contains the formula.
An overnight shift is negative or incorrect: Check that the formula uses MOD and that the start and end times are valid. A shift from 10:00 PM to 6:00 AM should return eight hours before deducting a break. If your formula was edited, restore the version shown in the steps. An identical start and end time returns zero, not 24 hours.
An employee’s weekly total is too low: Confirm that the name in the summary matches the schedule exactly, the date cells are real dates, the Monday date in J1 is correct, and the formula’s row ranges include every shift. Also check that Hours is numeric rather than text. If you schedule across multiple weeks, confirm that the summary uses the intended week-start date.
The printout cuts off columns: Choose Landscape under Page Layout > Orientation, set only the required cells as the print area, and use the print preview to inspect the result before printing. If the schedule is too small when fitted to one page, use multiple pages rather than shrinking text until it is unreadable.
Frequently Asked Questions
Can Excel create a work schedule automatically?
Excel can organize entries, calculate hours, and help you review coverage, but the steps here do not automatically assign shifts based on availability or staffing rules. You can use the table as a reusable template and enter assignments after deciding who should work each shift.
How do I schedule a shift that crosses midnight?
Enter the start and end times on one row and use the MOD-based formula in the guide. For example, 10:00 PM to 6:00 AM is eight hours before breaks. Decide whether the shift should count toward the week in which it starts; the sample weekly formula uses the start date.
Can I total hours for a whole month instead of a week?
Yes. You can adapt the SUMIFS date criteria to use the first day of the month and the first day of the next month as the end boundary. Keep the date cells as actual Excel dates and ensure the formula range includes all schedule rows.
Can employees update the schedule themselves?
Yes, if you share the workbook with appropriate access, but coordinate edits so changes do not conflict. In Microsoft 365, save it to OneDrive or SharePoint and use the Share button to invite people. Limit permissions when employees should be able to view but not change the schedule.
Does this method work in Excel for Mac or the web?
Yes. The table, time formats, and formulas work in current Excel for Mac and Excel for the web. Some ribbon labels or dialog layouts may differ, and the Mac shortcut for creating a table is Command+T rather than Windows Ctrl+T.
Related
- How to Copy Multiple Sheets in Excel Without Errors
- How to Copy Only Filtered Data in Excel Without Hidden Rows
- How to Copy Only Selected Cells in Excel Without Extra Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Sort Dates in Google Sheets: A Step-by-Step GuideTo sort data by date in Google Sheets, select the entire table, open Data > Sort range >…
- How to Add Superscript Numbers in Excel: Step-by-StepTo add a superscript number in Excel for Microsoft 365 on Windows, edit a text cell, select just…
- Import a CSV File into Excel: Steps, Settings, and FixesTo import a CSV file into Excel for Microsoft 365 on Windows, open a workbook, select Data >…
- Add a Footnote in Word: Shortcut and StepsIn Microsoft Word for Microsoft 365 on Windows, press Ctrl+Alt+F to insert a footnote at the cursor. Word…
- Excel Shortcut to Capitalize All Letters in a Cell?Excel has no dedicated keyboard shortcut that directly changes selected text to ALL CAPS. The quickest reliable method…
- Signature Line in Word Without the X Placeholder SymbolTo add a signature line in Word without the “X,” make a blank paragraph and apply a bottom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.