What this security guard attendance template does
This template records guard attendance by time, not just by code. Each row is one guard's duty: date, site, post, shift, the actual in and out times, and a status. From those, the workbook calculates hours worked, overtime, minutes late and minutes of early exit, then totals everything per guard (for payroll) and per site (for the client invoice). Night duties that start at 20:00 and end at 08:00 the next morning are handled correctly.
It complements our duty-code sheet rather than replacing it. If you bill clients per duty and pay guards per duty, the security guard attendance sheet format in Excel with its D/N codes is the simpler tool. If you bill by man-hours, pay overtime by the hour, or need to show late arrivals, use this time-based template. For handwritten site records, print the security guard attendance sheet PDF instead, and see our guide to every security guard attendance format for how the layouts fit together. Download this template with the button at the end of this page; it opens in Excel and Google Sheets.
The six tabs in the workbook
Grey cells hold formulas; white cells are for typing. The Daily Log ships with eight sample rows across two sites so you can see every formula working before you clear them.
- Start Here: setup steps and the status codes
- Daily Log: one row per guard per duty, 500 rows ready with formulas, dropdowns for shift and status codes
- Guard Summary: duties, night duties, hours, overtime, late arrivals, absences, leave and weekly offs per guard
- Site Summary: duties, night duties, man-hours, overtime and absences per site, the numbers an invoice is built from
- 8-Hour Shift Grid: a monthly code grid (I, II, III, R, WO, A, L) for posts run in three 8-hour shifts
- Lists: each shift code's scheduled in and out times and standard hours, which you edit to match your posts
Daily Log columns and the formulas behind them
You type Date, Site, Post, Guard ID, Guard name, Shift code, Actual in, Actual out, Status, Verified by and Remarks. Scheduled in, Scheduled out and Standard hours fill in from the Lists tab when you pick a shift code, so a D shift shows 08:00 to 20:00 and 12 standard hours unless you change them there.
Hours worked uses =ROUND(MOD(J6-I6,1)*24,2). MOD(...,1) is the part that handles night duties: a guard who clocks in at 19:56 and out at 08:04 the next morning gets 12.13 hours instead of a negative number. Overtime uses =FLOOR(MAX(0,L6-M6),0.5), which counts hours beyond the shift's standard hours in whole half-hours, so 8 extra minutes at handover is not overtime but staying until 21:30 on a 20:00 finish is 1.5 hours.
Late minutes use =MAX(0,ROUND((MOD(I6-G6+0.5,1)-0.5)*1440,0)). Shifting the difference by half a day before MOD means an early arrival for a night duty (19:52 for a 20:00 start) reads as early, not as 23 hours late. Early exit works the same way against the scheduled out time. Times must be typed as hh:mm, for example 07:52.
- Hours: =ROUND(MOD(Out - In, 1) * 24, 2), correct across midnight
- Overtime: =FLOOR(MAX(0, Hours - Standard hours), 0.5)
- Late (min): =MAX(0, ROUND((MOD(Actual in - Scheduled in + 0.5, 1) - 0.5) * 1440, 0))
- Status codes: P present, R reliever duty, A absent, WO weekly off, L leave, H holiday
Guard Summary and Site Summary: totals for payroll and invoices
Type each guard ID once in column A of the Guard Summary. Duties count the guard's P and R rows with COUNTIFS; night duties count N and III shifts actually worked; hours and overtime are SUMIFS over the log; late arrivals count rows more than 10 minutes late. The name fills in from the first matching log row.
The Site Summary does the same by site name. With the sample rows, Sunrise Heights shows 5 duties and 61.78 man-hours, which is the figure an hourly invoice starts from. Type site names exactly as they appear in the log, because 'Sunrise Heights' and 'Sunrise Hts' are two different sites to a formula. The guard billing calculator turns duties and man-hours into a monthly invoice.
Setting it up for 12-hour and 8-hour posts
Open the Lists tab before you start. D and N are set to 12 standard hours (08:00 to 20:00 and 20:00 to 08:00), G to 9 hours, and I, II and III to 8 hours (06:00, 14:00 and 22:00 starts). Change the times to match each client's posts. If one client's day duty starts at 07:00, add a code such as D7 with its own timings rather than editing D for everyone.
Check your wage structure before you rely on the overtime column. Many agencies pay a 12-hour duty as 8 normal hours plus 4 overtime hours; in that case set D and N to 8 standard hours and the template will show 4 hours of overtime on every full duty. The overtime pay calculator and our security guard salary calculation guide cover the pay side.
Using the template in Google Sheets
Upload the file to Google Drive and open it with Google Sheets. The formulas use only functions Sheets supports (MOD, FLOOR, COUNTIFS, SUMIFS, VLOOKUP, INDEX and MATCH), so the totals work the same way. Shared editing is useful when supervisors fill their own sites, but give each supervisor a separate copy or protect other sites' rows, because one wrong drag can overwrite a week of entries.
A month-end routine with the template
The template only helps if the log is filled from the site record, not reconstructed at the end of the month. A routine that works:
- Daily or every two days: enter the previous shifts from the site's daily register or the supervisor's report
- Weekly: filter the log for status A and for Late above 10 minutes, and follow up while people remember
- Month end: check each site's duties in the Site Summary against the sanctioned strength in the deployment order
- Month end: check that every R row has a matching WO, L or A row for the rostered guard on the same date and post
- Send the Site Summary with the client's countersigned sheet; send the Guard Summary to payroll
Mistakes that break the formulas
Almost every wrong total comes from data entry rather than the formulas. Watch for these:
- Times typed as 7.52 or '7:52 am' text instead of 07:52
- The same site spelled two ways, or guard IDs with stray spaces
- Typing over a grey formula cell
- Logging more than 500 rows without copying the formulas down and extending the summary ranges
- Leaving the sample rows in the log for the first month
When a spreadsheet stops being enough
A spreadsheet is only as good as the times typed into it. Once supervisors are copying times from phone calls and WhatsApp photos, the log looks precise but proves nothing, and a client who disputes four duties out of ninety has no timestamped evidence to look at. That is usually the point to capture attendance at the site instead: a security guard attendance app records each clock-in with a selfie and GPS position inside the site geofence, and exports the same guard-wise and site-wise totals. Our 7-day app setup plan shows how agencies make the switch without dropping the register.