Security Guard Billing Spreadsheet: Overtime, Holiday Premiums and Site-Specific Bill Rates

The short answer: a guard billing spreadsheet that holds up has four tabs. They are a rate table keyed by site, post and effective date, a holiday calendar for each contract, a shift log with one row per worked shift, and a calculation tab that splits each shift into regular, overtime and holiday hours before it applies any rate. The rule that matters most is this: work out pay overtime and bill overtime separately. They are different numbers. When a spreadsheet treats them as the same, it either loses money or produces invoices your clients will dispute.

The rest of this article shows how to build it, which formulas to use, and where these spreadsheets usually break.

Why one rate column is not enough

Most guard billing spreadsheets start as a single list: guard, site, hours, rate. That is fine until the first contract renewal changes a rate partway through the month, or a guard covers two sites in the same week, or a shift starts at 10 p.m. on December 31.

There are three separate questions behind every billed hour:

1. Which rate applies? That depends on the site, the post (an armed post and an unarmed lobby post at the same site often bill differently), and the date the shift was worked.
2. Is this hour overtime? That depends on the guard's total hours for the workweek, across every site they worked.
3. Can the client be charged the overtime or holiday premium? That depends on the contract, not on payroll.

If your spreadsheet cannot answer each question on its own, it will give wrong answers whenever the three overlap.

Tab 1: The rate table

Give each rate one row, with these columns:

• Site ID
• Post or position (for example, "Armed Patrol", "Front Desk", "Supervisor")
• Base bill rate
• Overtime bill multiplier (commonly 1.5, but take it from the contract)
• Holiday bill multiplier
• Overtime billable? (Yes, No, or "Client requested only")
• Effective start date
• Effective end date

Rate changes should never overwrite an old row. Close the old row by entering an end date, then add a new row. That way, when a client asks why March billed at the old rate, the answer is right there.

To find the right rate for a shift, match on site, post and date. In current Excel or Google Sheets, a FILTER or XLOOKUP that checks the shift date against the start and end dates works well. If you are on an older version, INDEX with MATCH on a helper key does the same job. If no rate matches, the lookup should return an obvious error, not zero. A silent zero is how unbilled hours go unnoticed for months.

Tab 2: The holiday calendar

Contracts do not agree on holidays. One client recognizes six, another recognizes ten, and a third pays a holiday premium only for overnight shifts on certain dates. Keep a table with one row per holiday for each contract: Site ID (or contract ID), holiday date, and holiday name.

Settle one rule in writing with each client: when does a holiday begin and end? Most contracts count midnight to midnight, but a 6 p.m. to 6 a.m. shift on Christmas Eve then has six holiday hours and six regular hours. Your spreadsheet has to split that shift, or it will bill all twelve hours one way or the other.

Tab 3: The shift log

Enter one row per shift actually worked, not per scheduled shift:

• Guard ID
• Site ID
• Post
• Shift start (date and time together)
• Shift end (date and time together)
• Unpaid break minutes
• Client requested overtime? (Yes or No)
• Source (timeclock, guard tour system, manual entry)

Keep the start and end as full date-time values. If you store only times, any shift that crosses midnight will break the arithmetic. If your data only has times, =MOD(End-Start,1)*24 gives the hours correctly, but full date-time values are still the better habit.

If a shift crosses midnight into a holiday, split it into two rows at midnight before it reaches the calculation tab. It feels tedious, but it is the cleanest way to make the holiday logic correct and easy to audit.

Tab 4: The calculation logic

This tab does the real work. Add these columns to each shift row.

Worked hours: =(End - Start) * 24 - Break / 60

Workweek start. This must match your company's fixed payroll workweek. If your workweek starts Monday: =INT(Start) - WEEKDAY(Start, 2) + 1

Prior hours this week. These are the guard's hours on earlier shifts in the same workweek, across all sites: =SUMIFS(Hours, Guard, this_guard, WeekStart, this_week, Start, "<" & this_start)

Overtime hours in this shift. This takes the part of this shift that falls above 40 weekly hours: =MAX(0, Prior + Hours - 40) - MAX(0, Prior - 40)

Regular hours: =Hours - OT_Hours

That overtime formula handles the hard case properly. When a shift pushes the guard from 36 to 44 hours, it assigns four hours as regular and four as overtime, and it does not mark the whole shift as overtime.

A note on the 40 hours: federal law in the United States generally requires overtime pay after 40 hours in a workweek, and some states add daily overtime or other thresholds. Check the rules for every state where your guards work, and add a daily overtime column if you need one. The formula above covers weekly overtime only.

Holiday flag: a COUNTIFS against the holiday calendar for this site and this shift's date.

Pay overtime versus bill overtime

This is the part most templates leave out, and it is where margin quietly disappears.

Say a guard works 32 hours at Site A from Monday to Thursday, then picks up a 12-hour fill-in at Site B on Saturday. The guard earns 4 hours of overtime pay, and those hours fall on Site B. But Site B did not cause that overtime. Your scheduling did. Unless Site B's contract says it pays premiums on any overtime hour, billing them 1.5 times the rate for those 4 hours invites a dispute you will probably lose.

So add one more column:

Billable OT hours: =IF(OT_Billable="Yes", OT_Hours, IF(AND(OT_Billable="Client requested only", ClientRequested="Yes"), OT_Hours, 0))

Any overtime that is not billable still costs you in payroll. Bill it at the regular rate, and track the unbilled premium as a cost. Over time that cost tells you which sites, schedules or supervisors create overtime the clients will not pay for.

Stacking holiday and overtime premiums

Decide, and write into each contract, what happens when one hour is both a holiday hour and an overtime hour. There are two common approaches:

• Greater of: bill the higher of the two multipliers.
• Stacked: apply both multipliers, which is less common and has to be stated explicitly.

Put the rule on the rate table as a column so the formula reads it, rather than hardcoding one approach for every client:

=IF(Stacking="Greater", MAX(OT_Mult, Hol_Mult), OT_Mult * Hol_Mult)

The invoice summary and the checks that catch errors

Build the invoice tab with a pivot table or SUMIFS by site, post and hour type (regular, billable overtime, holiday). Each line on the invoice should trace back to shift rows a client can check.

Before you send anything, run these checks:

• Missing rate: any shift where the rate lookup returned an error.
• Scheduled versus worked: compare the hours on the schedule with the hours actually worked, by site. Big gaps usually mean missed punches or coverage nobody recorded.
• Overlapping shifts: the same guard recorded at two sites at the same moment.
• Long shifts: anything over 16 hours, which is nearly always a missed clock-out.
• Unbilled overtime premium: the total from the section above, reviewed every pay period.

FAQ

Should overtime be billed by site or by guard? Overtime is worked out by guard, because the guard earns it across every site. Whether you can bill for it is decided by site, under that client's contract. Your spreadsheet needs both views.

What if a client's rate changes partway through a billing period? Close the old rate row with an end date, then add the new row. The date-based lookup will apply each rate to the correct shifts without any manual splitting.

Is a spreadsheet enough for this? For a small number of sites and guards, a well-built spreadsheet with the checks above works. It starts to strain when the shift log comes from several timeclock exports, when there are many contracts each with its own holiday rules, or when more than one person edits the file. Then version control and audit trails become the real problem.

Can I copy this structure into Google Sheets? Yes. Every formula here uses functions available in both Excel and Google Sheets, though older Excel versions may need INDEX and MATCH in place of XLOOKUP or FILTER.