Building an employee schedule in Google Sheets works well for small and mid-size teams who don't need expensive scheduling software. You get full control over the layout, you can share it with the whole team in seconds, and you can build in automatic calculations for hours, overtime, and cost that update as you edit the schedule.
The Schedule Layout
The most practical layout for a weekly schedule has employees in rows and days in columns. Each cell contains the shift (e.g., "9am–5pm") or is blank for a day off.
Employee | Mon | Tue | Wed | Thu | Fri | Sat | Sun
Jane Park | 9am–5pm | 9am–5pm | Off | 9am–5pm | 9am–5pm | |
Marcus Lee | 2pm–10pm | 2pm–10pm | 2pm–10pm | Off | 2pm–10pm | |
Use data validation on each cell with a dropdown of your standard shifts so managers pick from a list rather than typing free text. This keeps things consistent and makes calculations possible.
Calculating Hours Worked
To calculate hours from shift text, you either use a lookup table or store shifts as start/end times in separate columns. The lookup table approach is simpler: create a reference table mapping each shift label to its hours (e.g., "9am–5pm" = 8, "2pm–10pm" = 8, "6am–2pm" = 8). Then use VLOOKUP to pull hours:
=IF(B2="", 0, IFERROR(VLOOKUP(B2, ShiftHours!A:B, 2, FALSE), 0))
Do this for each day and sum across the row for weekly total hours. A helper row structure works: the visible row shows shift labels, a hidden row below it calculates hours using VLOOKUP, and a Total column sums the hidden row.
Flagging Overtime
Add an Overtime column:
=IF(H2>40, H2-40, 0)
Where H2 is total weekly hours. Apply conditional formatting to the Total Hours column — red if above 40, yellow if 38–40, green if under 38. This makes overtime visible immediately when you're building the schedule.
Calculating Labor Cost
Add an hourly rate to your employee list on a separate sheet. Pull it with VLOOKUP and multiply by weekly hours:
=H2*VLOOKUP(A2, EmployeeRates!A:B, 2, FALSE)
Sum the cost column to see total weekly labor cost. This is useful for budget management — you can see immediately if a schedule is over budget before you publish it.
Sharing the Schedule
Once the schedule is ready, go to File, Share, and set the link to "Viewer" access for employees. Anyone with the link can see the schedule on any device without needing a Google account. Update the sheet each week and the link stays the same — employees always see the current schedule at the same URL.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have employee and availability data in the sheet.
"I have a list of employees with their roles and hourly rates on Sheet 1. Build a weekly schedule template with shift assignments, automatic hours calculation, overtime flagging, and total labor cost."
SheetXAI builds the schedule layout, sets up the hours calculation, adds overtime flags, and calculates weekly labor cost.
Example 2: You want to generate a schedule from your HR or workforce management system.
"Pull employee availability and contracted hours from our workforce management system and build a weekly schedule with total hours per employee and a labor cost summary."
SheetXAI connects to your system, pulls the data, and builds the schedule.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Headcount Tracker in Excel, How to Use Conditional Formatting in Google Sheets, and Google Sheets AI Guide.
