A technician scheduling template for Excel and Google Sheets, and when to outgrow it

What the template needs to do

Most free scheduling templates are just a calendar grid with names typed in. They look fine and fail on the first busy Monday, because nothing stops you putting two jobs on one tech at the same time.

A useful template does four things:

• Holds a list of techs (or crews) with their working hours.
• Holds a log of jobs with a date, a start time, a length and an assigned tech.
• Works out the end time for you, including drive time.
• Warns you when two jobs overlap for the same person.

The same layout works for crews. Treat each crew as one "tech" row. I cover the crew version below.

The four tabs

Tab	Purpose
Techs	One row per tech or crew: name, skills, start time, end time, days off
Jobs	The log. One row per job. This is where all the formulas live.
Day view	Pick a date and see every tech's day in time order
Week board	Optional. Techs down the side, days across the top, a count of jobs and hours in each cell

Jobs tab columns

Row 1 is headers. Make the range a proper table or at least freeze the header row.

Column	Header	What goes in it
A	Job ID	A simple running number
B	Date	The service date
C	Tech	Dropdown from the Techs tab
D	Start	Start time, for example 9:00 AM
E	Hours	Expected work time, for example 2 for two hours
F	Drive buffer (hours)	Travel time after this job, for example 0.5
G	End (blocked until)	Formula: =D2 + (E2 + F2)/24
H	Customer	Name
I	Address	Where the tech goes
J	Job type	Dropdown: Repair, Install, Maintenance, Estimate
K	Status	Dropdown: Booked, En route, Done, Cancelled
L	Conflict?	Formula below

Spreadsheets store times as fractions of a day. One hour is 1/24. That is why the End formula divides by 24. Format column G as a time.

For the dropdowns, in Google Sheets use Data, then Data validation, then Add rule, then "Dropdown from a range". In Excel, use Data, then Data Validation, then List. Both are documented in the help pages listed in the sources. A dropdown means nobody types "Mike", "Mike S." and "mike" as three different people, which breaks every count later.

The double-booking check

This is the one formula that makes the template worth using. Put it in L2 and fill it down:

=IF(COUNTIFS($C:$C, C2, $B:$B, B2, $K:$K, "<>Cancelled", $D:$D, "<"&G2, $G:$G, ">"&D2) > 1, "CONFLICT", "")

In plain words: count the jobs for the same tech, on the same date, that are not cancelled, that start before this job ends and end after this job starts. The job always counts itself, so any total above 1 means an overlap. COUNTIFS is a function that counts rows meeting several conditions at once. It works the same in Excel and in Google Sheets, and it accepts conditions such as a "less than" test joined to a cell value.

Then add colour. In Google Sheets, select the Jobs range, go to Format, then Conditional formatting, choose "Custom formula is", and enter =$L2="CONFLICT" with a red fill. Google's help page describes this custom formula option. Excel has an equivalent conditional formatting rule that takes a formula.

A worked example with made-up jobs

Three techs, one Monday. The jobs below are made up to show the check at work.

Tech	Start	Hours	Drive buffer	Blocked until	Result
Maria	8:00 AM	2	0.5	10:30 AM	OK
Maria	10:30 AM	1.5	0.5	12:30 PM	OK
Dev	9:00 AM	2	0.5	11:30 AM	CONFLICT
Dev	11:00 AM	1	0.5	12:30 PM	CONFLICT
Sam	9:00 AM	3	0.5	12:30 PM	OK

Maria's two jobs fit because the second starts exactly when the first block ends. Dev's second job starts at 11:00 but his first job holds him until 11:30 once drive time is counted. Without the buffer column the sheet would show 11:00 against 11:00 and call it fine. That 30 minutes is the difference between a tech who arrives on time and one who calls the customer from the road.

Day view tab

Put a date in B1 and a tech name in B2. In A4 use the FILTER function, which returns only the rows of a range that meet your conditions. In Google Sheets:

=SORT(FILTER(Jobs!A2:K, Jobs!B2:B=B1, Jobs!C2:C=B2), 4, TRUE)

That lists one tech's jobs for one day in start-time order. Google's FILTER help page shows conditions combined this way. Print it or send the link as the tech's daily sheet. In Excel, the filter buttons on the Jobs table do the same job without a formula.

Week board tab

A week board is a quick scan, not the place you edit. Techs go in column A and the seven dates across row 1. In each cell, count the jobs:

=COUNTIFS(Jobs!$C:$C, $A2, Jobs!$B:$B, B$1, Jobs!$K:$K, "<>Cancelled")

Add a second block with the same layout that sums hours per tech per day, then colour any cell above the tech's normal day length. A tech with six jobs on Tuesday and one on Wednesday is a scheduling problem you can now see.

The crew and construction version

Construction jobs run for days, which breaks the one-row-per-job idea. Keep the formulas simple by using one row per crew per day, not one row per job. A three-day framing job is three rows. Add a column called Site and a column called Phase. Use "Crew 1", "Crew 2" instead of people's names, and add a second dropdown for the foreman. You lose a clean job view but you keep a working double-booking check, which matters more.

Common mistakes

• Typing times as text. If the Start cell is left-aligned, it is text and the formulas will not work. Re-enter it as a time.
• Forgetting travel time. It is the number one reason schedules slip.
• Deleting rows instead of cancelling. Set the status to Cancelled so you keep the history.
• Several people typing in one copy. Pick one person who books jobs. Everyone else reads.
• Trusting protection as security. Locking cells prevents accidents. Google notes it should not be used as a security measure.
• No emergency slot. Leave 1 to 2 hours a day empty for urgent calls, or every emergency breaks the day.

When to switch to software

A spreadsheet is fine for one dispatcher and a few techs. These signs say it is time to move:

• You have more than about five techs, or two people booking at once.
• Techs need the schedule on their phones with the address and job notes.
• Customers ask for arrival windows, reminders or texts.
• You re-type the job into an invoice after the visit.
• You are spending more than an hour a day fixing the schedule.

The next step is a scheduling or field service tool. I compare the main options, with prices, in my guide to technician scheduling software. If you run crews rather than individual techs, read crew scheduling software. If you want one person deciding who goes where, see dispatch software for small business. For the full picture of what these tools cover, there is field service management software. For AI that suggests assignments and fills gaps, see my AI scheduling assistant page.

If the spreadsheet fits your business but has outgrown being a spreadsheet, you may not need to buy anything. My guide on turning an Excel sheet into a web app shows how to keep your own rules and add logins, phone access and reminders.

How I'd approach this

Use the template for a month, with real jobs, before you spend money. Two things will show up: how many jobs you really book a day, and where the double-bookings come from. If the conflicts come from walk-in phone calls with no one entering them, software will not fix that. A process will.

Set the drive buffer from real data. Measure real drive times for a week and set the buffer to your average, not your best day.

When the sheet starts to hurt, decide between a ready-made tool and something built around how you work. A ready-made tool is faster to start. A custom build is better if your scheduling rules are unusual, such as skills matching, service contracts or crews that travel together. If you are not sure which side you are on, book a quick call with me, Ranjit and bring your current sheet.