An employee shift schedule template in Excel can be a simple weekly grid for assigning shifts, supported by a separate log for calculating hours. In Microsoft Excel for Microsoft 365 on Windows, set up columns for employee, date, start time, end time, unpaid break, and calculated hours; then use filters and a weekly total to review the schedule before sharing it.
What an Excel shift schedule should include
A useful schedule needs to answer two questions quickly: who is working, and when are they working? A calendar-style grid makes assignments easy to scan, while a shift log keeps the underlying data consistent enough to total and sort. For a small team, one worksheet can show both. For a larger team or a schedule that changes often, separate the readable schedule from the detailed shift records.
Start with a weekly planning sheet. Put the week’s date range at the top, employee names down the first column, and one column for each day. Enter a shift label such as “7:00 AM–3:00 PM,” “OFF,” or “PTO” in each employee-day cell. Include the employee’s role or work area if people can be assigned to different duties. A second worksheet called Shift Log can hold one row per shift with these fields:
- Employee: Use a consistent name or employee ID, not variations such as “Sam,” “Samuel,” and “S. Lee.”
- Date: Store a real Excel date, not a date typed as part of a text label.
- Start and end: Enter real Excel time values, so calculations can use them.
- Unpaid break: Record the break duration consistently, for example as decimal hours.
- Role, location, or notes: Add only fields that help plan or communicate work.
- Hours: Calculate scheduled hours in a formula column rather than repeatedly calculating them by hand.
This structure is a template, not a complete workforce-management system. It helps organize assignments and estimate scheduled hours, but does not by itself establish whether a schedule meets every workplace rule or agreement.
Set up the workbook and weekly view
Create a workbook with a Schedule worksheet and a Shift Log worksheet. In the weekly view, put actual dates in the column headings, such as Monday’s date, rather than relying only on weekday names. This prevents confusion when the schedule is copied to another week. You can format the dates to show the weekday and date together, while keeping the underlying values as dates.
For the detailed records, put headers in row 1 and one shift per row underneath. For example, columns A through F can be Employee, Date, Start, End, Unpaid break (hours), and Paid hours. Select the range and choose Home > Format as Table. Confirm that the table has headers. An Excel table expands as you add rows, carries formatting down, and makes it easier to filter records. Give it a clear name from the Table Design tab if you plan to refer to it in more advanced formulas.
Make the sheet easy to use before adding data:
- Format Date as a date and Start and End as times. In the Windows Format Cells dialog, Ctrl+1 opens number-format options; on Mac, Command+1 opens Format Cells.
- Use a consistent time convention, such as 7:00 AM, and make clear whether a break entry is in minutes or decimal hours.
- Freeze the header row so column names remain visible while scrolling. Select cell A2, then choose View > Freeze Panes > Freeze Panes.
- Use filters to show one date, employee, or role at a time. Avoid merged cells in the data log because they make sorting and filtering harder.
Enter shifts consistently and reduce errors
Excel stores dates and times as values, even when they are displayed in a familiar format. Typing 7:00 AM into a properly formatted time cell creates a value Excel can calculate with. Typing a combined label such as “7 AM to 3 PM” into one cell creates text; it is fine for a visual-only grid, but it cannot be used directly as two separate time values in an hours formula.
For a small schedule, a dropdown can help standardize entries in the weekly grid. Put approved labels such as AM, PM, Overnight, OFF, and PTO in a list on a supporting worksheet. Select the cells where staff will enter labels and use Data > Data Validation to create a list validation rule. This helps avoid inconsistent spellings, but labels alone do not calculate hours. Keep start and end times in the shift log if totals matter.
Separate scheduled work from absence or availability codes. OFF and PTO are not start or end times, so do not put those labels into time fields. If leave hours need to count toward a particular report, record them in a clearly defined field or separate category rather than disguising them as a worked shift. Decide who can edit the workbook and who is responsible for publishing the final version; a shared file is only reliable when people know which copy is current.
Calculate hours, including overnight shifts
With employee in A, date in B, start time in C, end time in D, unpaid break in E as decimal hours, and paid hours in F, enter a calculation in F2 and fill it down. This formula assumes each shift is shorter than 24 hours and that breaks are entered as numbers such as 0.5 for half an hour:
=24*MOD(D2-C2,1)-E2
For a shift from 10:00 PM to 6:00 AM with a 0.5-hour unpaid break, the result is 7.5 paid hours: eight hours elapsed minus half an hour. Format the result as a number, such as 1 or 2 decimal places, because the formula returns hours as a decimal. If you instead want a duration displayed as hours and minutes, use a time-duration approach and a custom format such as [h]:mm; be consistent about the units used for break entries.
The MOD calculation handles an end time that is earlier on the clock than the start time by treating it as the following day. It assumes a shift lasts less than 24 hours. For shifts that can span 24 hours, store full start and end date-time values, including dates, and calculate the elapsed time from those values instead. Also check for missing or mistyped times: an empty time cell can produce a misleading number, so a blank or error check may be appropriate if the log is used for formal reporting.
To total one employee’s scheduled hours for a seven-day period, place that employee’s name in H2 and the first date of the schedule week in J1. With employee names in A2:A200, shift dates in B2:B200, and calculated hours in F2:F200, use:
=SUMIFS($F$2:$F$200,$A$2:$A$200,$H2,$B$2:$B$200,">="&$J$1,$B$2:$B$200,"<"&$J$1+7)
If the five shifts in that date range are 8, 8, 7.5, 8, and 6 hours, the total is 37.5 hours. The date criteria include the start date and exclude the date seven days later, so the formula works for a week with seven consecutive calendar dates. Make sure the date column contains actual Excel dates and that the total-hours column contains numbers, not text.
Check coverage and make the schedule readable
Hours totals tell you how much work is assigned to each person, but they do not tell you whether the right number of people are present at the right time. A weekly grid is helpful for scanning assignments; a coverage check needs to compare scheduled workers with the requirement for each time period and role. For example, a shop may need two people at opening and three during a busy afternoon. A weekly total can look reasonable even if an individual time block is understaffed.
For a small team, add a coverage row under each day or maintain a second table listing time blocks, required headcount, and assigned headcount. Count only employees whose shifts overlap the block, and account for role qualifications where relevant. Excel can support more elaborate overlap calculations, but the exact method depends on how shifts, breaks, and roles are recorded. Verify the result against actual assignments instead of assuming that a filled shift cell means the coverage requirement is met.
Use formatting to make important information visible without making the sheet noisy. Apply distinct, consistent fills to shift categories, make OFF days visually different, and keep text readable when printed. Conditional formatting is available from Home > Conditional Formatting; use it for a limited set of meaningful alerts, such as a weekly-hours threshold that a manager wants to review. A highlighted threshold is a prompt to check the schedule, not proof that a schedule is compliant or noncompliant.
Review the schedule before sharing it
Before publishing a weekly schedule, check names, dates, shift times, breaks, role assignments, and weekly totals. Look for blank cells that have not been decided, overlapping assignments, and overnight shifts that cross a date boundary. If employees need to see only the final schedule, hide or separate planning notes that should not be distributed. Avoid placing sensitive personal information in a broadly shared workbook.
Excel for Microsoft 365 can store a workbook in OneDrive or SharePoint for shared access, subject to the organization’s permissions and policies. If multiple people edit the file, communicate whether edits are allowed after publication and how changes will be announced. A schedule exported or printed as a PDF is convenient to read, but it is a snapshot: later edits to the workbook will not update that PDF automatically. Include the week’s dates and a revision date or status so readers can recognize an outdated copy.
For printing, check the page layout and print preview rather than assuming the full weekly grid will fit. Use landscape orientation or adjust scaling when appropriate, but avoid shrinking text until it becomes difficult to read. A digital schedule can preserve filters and more detail; a printed copy should prioritize the essential information employees need at a glance.
Common template problems and practical fixes
- Hours appear as a time of day: The result cell may be formatted as a clock time. Use a decimal number format for decimal hours, or a duration format for elapsed time.
- Overnight hours look negative: A direct end-minus-start calculation does not account for crossing midnight. Use the MOD method for shifts under 24 hours, or record full date-time values.
- Totals omit a shift: Check that the employee name matches exactly, the date is a real date in the chosen week, and the hours are numeric. Extra spaces or inconsistent names can prevent a match.
- Copied weeks have wrong dates: Build each week from actual dates and verify the date headings after copying. Do not rely on text labels alone.
- The grid is difficult to maintain: Keep one shift per row in the log, avoid merged cells in the data, and use filters or a formatted table instead of manually rearranging records.
- Scheduled hours are mistaken for payroll hours: A schedule estimates planned work. Actual time worked, rounding, paid breaks, overtime, and payroll rules may be handled differently. Do not treat a template’s total as an official payroll result without confirming the applicable process.
Rules for breaks, rest periods, overtime, and schedule notice vary by location and by workplace policy or agreement. An Excel workbook can help flag items for review, but a generic template cannot determine the requirements that apply to every employer. Confirm relevant requirements with an appropriate local authority or qualified professional.
When Excel is enough—and when to consider another tool
Excel is a reasonable fit when a team has a manageable number of employees, a predictable schedule cycle, and a person responsible for maintaining the workbook. It is flexible, easy to adapt, and useful for printing or analyzing planned hours. A simple spreadsheet can also be a sensible starting point before deciding which information a more specialized process needs.
Consider dedicated scheduling software when several managers edit schedules, shifts change frequently, employees need to submit availability or swap shifts, or the organization needs automated notifications and integrations. Such systems may include features a spreadsheet does not provide by default, but their suitability and price depend on the product and the organization. Compare the time spent maintaining the workbook, the risk of outdated copies, access controls, reporting needs, and any subscription or setup costs before switching. Excel remains useful for a small operation, but it should not be presented as automatically enforcing workplace rules or notifying employees of changes.
Frequently Asked Questions
How do I make a simple employee shift schedule in Excel?
Put employee names in rows and the dates for one week in columns, then enter a shift label in each employee-day cell. For totals, keep a separate log with one row per shift and separate date, start, end, break, and hours fields.
How do I calculate hours for an overnight shift in Excel?
For start and end times in C2 and D2, and an unpaid break in E2 entered as decimal hours, use =24*MOD(D2-C2,1)-E2. It handles a shift crossing midnight when the shift is shorter than 24 hours.
Can Excel calculate weekly hours for each employee?
Yes. Record each shift as a row, calculate its hours, and use SUMIFS to add hours for a selected employee and date range. The employee name and week start date must match the values in the shift log.
Should I put the whole shift in one cell?
It is fine in a display-only schedule, for example as “9 AM–5 PM.” For reliable hour calculations, store start and end as separate Excel time values in a shift log; a text label is not a substitute for two time values.
Is an Excel shift schedule template suitable for payroll?
It can estimate scheduled hours, but a schedule is not necessarily a record of hours actually worked and does not automatically account for every payroll rule. Confirm the organization’s timekeeping and payroll process before using spreadsheet totals for pay.
Can employees edit a shared schedule?
A workbook stored in OneDrive or SharePoint can support shared editing when permissions and organizational settings allow it. Decide who may edit, how changes are communicated, and whether employees should receive a read-only or published copy.
Related
- Excel Data Validation Disabled? Causes and Fixes
- Excel Data Cleaning Freelancer Jobs: Skills and Rates
- Daily Vehicle Checklist Template Excel: What to Include
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Convert Excel to PowerPoint: Methods, Steps, and TipsTo convert Excel content into a PowerPoint presentation, select the cells or chart in Excel, copy them, and…
- Corrupted Excel File Repair: What to Try FirstFor a corrupted Excel file, first make a copy of the file, then try Excel’s built-in Open and…
- Create an Outlook Email Template: Step-by-StepTo create a reusable email template in classic Outlook for Windows, compose a message, then choose File >…
- Excel Sheet Template for Budget: How to Build OneThe fastest way to get an Excel budget template is to open Excel and search the built-in template…
- Excel Custom Number Format for Thousands (k)To show Excel numbers in thousands with a “k” suffix without changing their stored values, select the cells,…
- How to Close an Open Dialog Box in ExcelTo close an open dialog box in Excel for Microsoft 365 on Windows, press Esc, click the dialog’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.