How to Make a Work Schedule in Google Sheets: A Guide

To make a work schedule in Google Sheets, create a table with one shift per row, add columns for the date, employee, start and end times, and breaks, then format, check, and share it. You can add a formula to calculate shift hours and use filters or dropdowns to make the schedule easier to manage. Start with the people, dates, availability, and scheduling rules you need to account for.

What You’ll Need

A Google account and access to Google Sheets in a web browser.
The schedule period, such as one week or one month, plus the names of employees and the roles or locations they cover.
Availability, approved time off, required coverage, and any break or shift-length rules you need to follow.
Optional: a current schedule or blank spreadsheet to use as a reference. Do not include sensitive employee details unless everyone who can access the file is authorized to see them.

Step-by-Step

Create a spreadsheet and name it. Open Google Sheets, choose Blank spreadsheet, and give the file a clear name, such as “Store Schedule — Week of May 4.” The name should identify the team and schedule period so people can find the right version in Drive. If you already have a workbook, you can add a new sheet using the plus button at the bottom. Keep one tab per week or pay period if that suits your team; label each tab with its date range.
Set up a simple, consistent table. In row 1, enter headers such as Date, Employee, Role, Start, End, Unpaid break (hours), Shift hours, and Notes. Put each individual shift on its own row below the headers. This layout works well for a team of any size and makes it straightforward to sort, filter, and total hours. If one employee works two separate shifts in a day, use two rows rather than combining them in one cell.
Enter the dates and shift details. Add one row for every shift you plan to assign. Enter actual dates in the Date column, employee names consistently, and start and end times in the Start and End columns. For example, enter 9:00 AM and 5:00 PM as time values, not as notes in a sentence. Use the Notes column for useful details such as a handover, location, or training assignment. Leave unassigned shifts visible and mark them “Open” so coverage gaps are not mistaken for free time.
Format dates, times, and the header row. Select the date cells and use Format > Number > Date. Select the start and end cells and choose Format > Number > Time. Choose a time display that your team can read easily, such as 1:30 PM or 13:30. Select row 1, make the text bold, and apply a fill color using the toolbar. Freeze it with View > Freeze > 1 row so the headings stay visible as you scroll. Formatting changes how values appear; it does not turn text that looks like a date or time into a usable date or time value.
Add a shift-hours calculation if it will save time. With Date in column A, Employee in B, Start in D, End in E, Unpaid break in hours in F, and Shift hours in G, enter this in G2: =MOD(E2-D2,1)*24-F2. This assumes D2 and E2 contain real time values and F2 contains a break expressed as hours, such as 0.5 for 30 minutes. For a 9:00 AM–5:00 PM shift with a 0.5-hour unpaid break, the result is 7.5 hours. MOD also allows an overnight shift, such as 10:00 PM–6:00 AM, to calculate as eight hours before the break is subtracted. Drag the small blue fill handle at the lower-right corner of G2 down the column to copy the formula to other shift rows. Check a few results against the schedule before relying on them.
Calculate a weekly total where useful. If one employee’s shifts occupy G2:G8, enter =SUM(G2:G8) in an empty cell below or beside those entries. This assumes the rows contain only that employee’s shifts for the period. For example, shift-hour values of 7.5, 8, 8, 6, and 7.5 add to 37 hours; blank cells in the remaining rows do not change the total. For a shared schedule with several employees, filter the Employee column to review one person’s rows, or create a separate summary area rather than adding every worker’s hours together.
Use filters to review the schedule. Select the header row and the schedule data, then choose Data > Create a filter. The filter icons let you show a particular employee, date, or role without deleting other rows. Sort by Date to put shifts in chronological order, or filter for “Open” entries to find unfilled work. Be careful when sorting: include the complete table so that dates, names, and shift times stay together. If people will edit the schedule at the same time, agree on who is allowed to sort or edit shared rows.
Add dropdowns for repeated entries. Dropdowns help keep role names and status labels consistent. Select the cells in the Role column, choose Data > Data validation, then add a rule with a dropdown and enter the role choices your team uses, such as Cashier, Supervisor, or Stock. You can repeat this for a Status column with options like Assigned and Open. Apply the rule to enough rows for the schedule, and test a cell to confirm the choices appear. Keep free-text Notes separate so a dropdown does not restrict useful shift details.
Highlight important details without clutter. Use Format > Conditional formatting to distinguish open shifts, weekends, or roles that need attention. Select the cells or range first, then choose a rule that matches your data, such as text containing “Open,” and select a noticeable fill color. Use a small number of colors and include a short legend if color has a specific meaning. Do not rely on color alone: keep a written status such as “Open” in the cell so the schedule remains understandable when printed or viewed by someone who cannot distinguish the colors.
Check coverage and share the schedule. Review each day against the coverage you need, employee availability, approved time off, and your organization’s scheduling requirements. Check that the shift hours and break entries are sensible, then fix missing or conflicting details before publishing. Use the Share button to invite the appropriate people by email. Choose viewer access for people who only need to read the schedule and editor access only for people who should change it. Tell staff where the final schedule is and when it was updated; a link to an old tab or an unannounced edit can cause confusion.
Prepare a version people can read away from a screen. If your team needs a paper copy, choose File > Print. In the print settings, check the selected sheet, page orientation, scale, and margins, and use the preview to see whether names or notes are cut off. A wide schedule may fit better in landscape orientation or when scaled to fit the page. If it becomes too small to read, print fewer columns or use a larger page rather than shrinking the text excessively. Keep the Google Sheets version as the working copy so later changes are not lost in an outdated printout.

Common Mistakes

Typing dates and times as plain text: Entries such as “Tuesday afternoon” may be understandable to a person but cannot be reliably sorted or used in calculations. Enter a date or time value in its own column, then format it for readability.

Combining several shifts in one cell: A cell containing “9–1, 2–5” is hard to filter and calculate. Create a separate row for each shift, even when the employee and date are the same.

Sorting only one column: Sorting names without the matching shift details can scramble the schedule. Select the whole table before sorting, or use the filter controls on the header row.

Forgetting break units or overnight shifts: Decide whether breaks are entered as hours or as a time value and label the column clearly. The example formula expects decimal hours. Verify overnight calculations and make sure your time entries are actual time values.

Giving everyone edit access: Accidental changes can be difficult to spot. Share viewer access with most readers and limit editing to the people responsible for maintaining the schedule.

Tips

Make a clean template tab, then duplicate it for each new schedule period. Update the dates and clear old assignments while preserving headers, formatting, dropdowns, and formulas.
Use a consistent naming convention for tabs, such as “May 4–10,” and include the year in the file name or date range when schedules may be referenced later.
Keep the table structure stable. Avoid merged cells in the schedule data because they can interfere with filtering, sorting, and copying rows.
Choose readable colors with enough contrast and use the same meaning every week. For instance, reserve one color for open shifts rather than inventing a new color code for each schedule.
Protect the formula column from casual edits if people need edit access. In Google Sheets, select the relevant range and use Data > Protect sheets and ranges; review the permissions carefully, since protection is intended to control editing, not to hide information.
Keep a clear change process. Ask staff to report availability or corrections by a stated deadline, and note when a published schedule changes so people know to check the latest version.

Troubleshooting

The hours formula shows an unexpected number or an error. Check that the start and end cells contain times, not text, and that the break is entered as a number of hours. For a half-hour break, enter 0.5 when using the example formula. Confirm the formula references the right columns and row, then test it on one ordinary shift and one overnight shift. If a cell is formatted as a time, a decimal hour total may display oddly; select the total and choose Format > Number > Number.

The date or time will not sort correctly. The values may have been pasted as text. Select a problem cell and check whether changing its number format changes how it displays. If not, re-enter the value or use Format > Number to apply the appropriate date or time format after entering it correctly. Also check that the entire schedule range is included in the sort.

A dropdown is missing or has the wrong choices. Select a cell that should have a dropdown and open Data > Data validation to review its rule and applied range. The rule may not extend to newly added rows, or it may be attached to a different column. Expand the range or copy a correctly configured cell into the needed rows, then test the dropdown.

People cannot open or edit the file. Use Share to check the recipient’s email address and access level. If the file is restricted to specific accounts, a person signed in with a different Google account may be blocked. Ask them to switch accounts or request access; do not make the whole file public just to solve one access issue.

The printed schedule is cut off or too small. Return to File > Print and inspect the preview. Try landscape orientation, narrower margins, or printing only the relevant columns. If fitting the full sheet makes the text unreadable, use more than one page or distribute the schedule digitally.

Frequently Asked Questions

Can I make a weekly work schedule in Google Sheets for free?

Yes. You can create and edit a schedule in Google Sheets with a Google account. Sharing and simultaneous editing are useful for teams, but each person needs the appropriate access to the file.

What is the best layout for a staff schedule?

For a schedule you need to sort, filter, or calculate, use one shift per row with separate columns for the date, employee, start, end, break, and hours. A calendar-style grid can be easier to scan at a glance, but it is less flexible for filtering and shift calculations.

Can Google Sheets calculate overnight shifts?

Yes. The example formula uses MOD to handle an end time that is earlier than the start time, as happens when a shift crosses midnight. Make sure both entries are time values and check that the break is entered in the units the formula expects.

How do I schedule recurring shifts?

Duplicate a template tab or copy the relevant rows into the next schedule period, then update the dates and review employee availability. Check each copied shift rather than assuming availability and coverage have stayed the same.

How can employees see the schedule without changing it?

Click Share, add the employees who need access, and set their role to Viewer. They can read the schedule without editing cells. If you make a new file or change its sharing settings, confirm that the link still points to the current schedule.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.