Most volunteer programmes track hours on a clipboard at the sign-in table: name, date, time in, time out. It works until someone needs a total. Then a coordinator types a month of handwriting into a spreadsheet, finds rows with no time out, and has to decide what to do about them. Food banks, charity shops, hospital volunteer services, animal shelters, churches and clean-up crews all hit the same three problems: totals that take hours to build, names spelled three ways, and missing sign-outs.
This guide covers the free routes first, then the formulas that turn sign-in and sign-out rows into hours per shift and per month, then where the free routes break and where a badge scanner fits. The formulas work on rows from any source, including a Google Form, a typed-up paper sheet or a scanner.
Written by QR to Sheets, which sells the scanner link described later in this guide, so weigh that accordingly. The Google Form and paper routes come first, and for a small team they are genuinely enough. The formulas below work whichever way the rows arrive.
Can a free Google Form or a paper sheet track volunteer hours?
Yes. A paper sign-in sheet costs nothing and needs no signal. Type each line into a Log tab later: the date and time in column A, the volunteer's ID in column B, and Sign in or Sign out in column C. A date and time typed as 2026-10-08 09:00 becomes a real date-time in Google Sheets, so the formulas below work on it directly.
A Google Form is the free digital version, and here it is fine for volunteers to use their own phones. Make two questions, in this order: a dropdown with each volunteer's ID or exact name, matching column A of the Roster, and a choice of Sign in or Sign out. Print the form's link as a QR poster at the sign-in table. Responses land in a linked spreadsheet with the timestamp in column A, the volunteer in column B and the direction in column C. Google Forms timestamps are real date-times, so no conversion is needed.
- Put the volunteer question first and the direction second, so the response sheet's columns line up with the formulas below.
- Use a dropdown, not a typed name. Typed names do not match: Ana, Anna and ana lopez are three volunteers to a formula.
- A tablet at the sign-in table with the form open works as a free kiosk for volunteers without a smartphone, one person at a time.
How do I turn sign-ins and sign-outs into hours per shift?
Leave the raw rows alone and build a Calc tab beside them. Type the headers Time, ID, Link, Signed out, Status and Hours into A1 to F1. Then copy the rows across in A2, converting the time as you go.
- From QR to Sheets rows on a tab named Log, in Calc!A2: =FILTER({ARRAYFORMULA(IFERROR(VALUE(Log!A2:A), "")), Log!B2:C}, Log!A2:A<>"")
- From a Google Form, in Calc!A2: =FILTER('Form Responses 1'!A2:C, 'Form Responses 1'!A2:A<>""), and in the formulas below replace link:Sign in with Sign in and link:Sign out with Sign out.
- From a typed-up paper sheet on a Log tab with Sign in and Sign out in column C: the Google Form version, pointed at Log.
- Format Calc columns A and D as Format, Number, Date time.
Now three formulas in row 2, filled down well past the last row you expect, say to row 2000. Rows with no data stay blank.
- Signed out, in D2: =IF(OR($A2="", $C2<>"link:Sign in"), "", IF(COUNTIFS($B:$B, $B2, $C:$C, "link:Sign out", $A:$A, ">"&$A2, $A:$A, "<"&(INT($A2)+1))=0, "", MINIFS($A:$A, $B:$B, $B2, $C:$C, "link:Sign out", $A:$A, ">"&$A2, $A:$A, "<"&(INT($A2)+1))))
- Status, in E2: =IF($A2="", "", IF($C2="link:Sign in", IF(D2="", "Check: no sign-out", IF(COUNTIFS($B:$B, $B2, $C:$C, "link:Sign in", $A:$A, ">"&($A2+1/172800), $A:$A, "<"&D2)>0, "Check: signed in again, not counted", "OK")), IF(COUNTIFS($B:$B, $B2, $C:$C, "link:Sign in", $A:$A, "<"&$A2, $A:$A, ">="&INT($A2))=0, "Check: sign-out with no sign-in", "")))
- Hours, in F2: =IF(E2="OK", ROUND((D2-$A2)*24, 2), "")
How it works: for each sign-in row, MINIFS finds the same volunteer's earliest sign-out that is later than the sign-in and on the same day. INT($A2)+1 is midnight at the end of that day, so a forgotten sign-out on Saturday never pairs with next week's sign-out to make a week-long shift. The 1/172800 in the Status formula is half a second: it stops a sign-in from counting itself as a later sign-in when Sheets rounds its time inside the comparison. Each OK row is one shift, and the hours are the difference multiplied by 24, rounded to two decimals. Two shifts on one day pair correctly, and because the formulas compare times rather than row positions, a row that reached the sheet late still pairs with the right partner.
The status column catches the three things that go wrong. A double tap at the sign-in table, or a volunteer who forgot to sign out in the morning and signed in again after lunch, leaves two sign-ins before one sign-out; only the later one counts, and the earlier one is marked to check. A sign-in with no sign-out that day is marked Check: no sign-out. A sign-out with no sign-in that day is marked too. The same pairing logic, with more on overnight shifts and rounding, is in the guide to calculating hours from scan rows.
The same-day rule suits daytime shifts. For an overnight shelter or a night-time event that crosses midnight, the pairing needs a different window; that guide's MINIFS without the end-of-day condition is the starting point, with the trade-off that a missed sign-out can then pair with a later shift, so check the long ones by eye.
How do I total volunteer hours per month?
Add a Month tab. Type the first day of the month, such as 2026-10-01, into B1. In row 3 type the headers ID, Name, Hours, Shifts and To check. In A4, list every volunteer with =FILTER(Roster!A2:B, Roster!A2:A<>""). Then in row 4, filled down as far as the list goes:
- Hours, in C4: =SUMIFS(Calc!$F:$F, Calc!$B:$B, $A4, Calc!$A:$A, ">="&$B$1, Calc!$A:$A, "<"&EDATE($B$1, 1))
- Shifts, in D4: =COUNTIFS(Calc!$B:$B, $A4, Calc!$E:$E, "OK", Calc!$A:$A, ">="&$B$1, Calc!$A:$A, "<"&EDATE($B$1, 1))
- To check, in E4: =COUNTIFS(Calc!$B:$B, $A4, Calc!$E:$E, "Check*", Calc!$A:$A, ">="&$B$1, Calc!$A:$A, "<"&EDATE($B$1, 1))
- EDATE($B$1, 1) is the first day of the next month, so the range is the whole month. Change B1 to look at another month; for a year to date, replace EDATE($B$1, 1) with the first day of next year.
- A volunteer with nothing that month shows 0 hours and 0 shifts, which is itself useful: it is the list to thank, check in with or take off the rota.
To see every row that needs attention in one place, put =FILTER(Calc!A2:E, LEFT(Calc!E2:E, 6)="Check:") on a Review tab. Sort the Month tab by hours if you want the most active volunteers at the top.
What do I do about a missing sign-out?
Flag it and ask; do not guess. A missing sign-out counts as zero hours in the totals above, which is deliberately visible in the To check column rather than quietly filled with a standard shift length. Filling it in with a typical shift makes every total look complete and makes none of them trustworthy.
- Ask the volunteer, or the shift lead who was there, what time they left.
- Record the answer on a Corrections tab: the ID in column A, the date in column B, the confirmed hours in column C, who confirmed it in column D and a note in column E.
- Never type a made-up time into the raw log. The log is the record of what was actually scanned or submitted; corrections sit beside it with a name against them.
- To include corrections in the monthly total, add this to the end of the Hours formula: + SUMIFS(Corrections!$C:$C, Corrections!$A:$A, $A4, Corrections!$B:$B, ">="&$B$1, Corrections!$B:$B, "<"&EDATE($B$1, 1))
Where do the free routes break?
- Paper has to be typed up, and handwriting at 7 am is where most missing sign-outs and unreadable times come from.
- A Google Form relies on every volunteer remembering to submit twice, once at each end of the shift, on their own phone with signal. Signing out is the step that gets forgotten on the way to the car.
- Choosing a name from a dropdown of eighty volunteers is slow, and a mis-tap credits the hours to the wrong person with nothing to show it happened.
- It is self-reported. A form can be submitted from home or for a friend, and every response looks identical.
- A shared tablet with a form open handles one volunteer at a time, which is a queue at the start of a big shift.
Where does a badge scanner fit?
The scanned version turns the work around. Instead of each volunteer filling something in, one phone or tablet at the sign-in table scans a printed badge for each volunteer. The badge holds only the ID, never a name or phone number, so a lost badge reveals nothing. The free QR code ID card maker turns a pasted list of names and IDs into printable badges in the browser, with no signup; a keyring card or a lanyard both work.
In QR to Sheets you connect the programme's Google Sheet once, then create two scanner links named Sign in and Sign out, both writing to the same tab, here called Log. Each scan adds a row: the scan time in column A, the badge ID in column B and the link in column C. Column C holds link: followed by the link's name, so the rows read link:Sign in and link:Sign out, which is exactly what the Calc formulas above match. Columns D, E and F hold the workspace name, the device type and a scan ID.
- The time is written as text in the form YYYY-MM-DD HH:MM:SS in your workspace timezone, which is why the Calc tab converts it with VALUE(). Set the timezone in Settings before the first shift; timestamps in the wrong timezone explains what goes wrong when it is left on UTC.
- If the Log tab was empty when scanning started, the first scan lands in row 1. Type the six column names, scanned_at, qr_value, scanner_email, organization, device_type and scan_id, into row 1 first so every formula can start at row 2.
- A forgotten badge is not a problem: the scanner has a manual entry field, so the coordinator types the ID and the row is written the same way.
- Repeats are recorded, not refused. A badge scanned twice on the same link within 12 hours shows an amber notice and the row is saved anyway; the Calc tab counts only one sign-in. Keep the link's Accept duplicate scans setting on, because switched off a link records each code once, ever, and would refuse every volunteer's second shift.
If the signal drops, the open scanner keeps saving scans on the phone and sends them when the signal is back, each with its original scan time. Those rows can land lower in the sheet than later scans, which the Calc formulas already handle because they compare times, not row order.
Who scans, and on which phone?
The coordinator scans, or volunteers hold their badge up to a tablet on a stand at the sign-in table. Volunteers do not open the scanner link on their own phones. Each phone that scans counts as one device. Free covers one phone; Premium is $7 per phone per month. People being scanned never open the link and need nothing.
- One coordinator phone or one tablet is one device even though it uses both links. Open both links in the same browser and switch between them; a link opened in another app or a private tab counts as a new device. A tablet left on a stand needs a clear label saying which link is open; the guide to a shared check-in tablet covers the practical side.
- Forty volunteers scanning on their own phones would be forty devices. If volunteers must use their own phones, that is the Google Form route above, which costs nothing.
- The free plan allows one active scanner link, one phone and 300 scans in total, not per month, so it runs one direction only: a sign-in record without hours. Sign-in and sign-out together need the paid plan, 7 USD a month for one phone at the table, with unlimited scans.
| Capability | Paper sheet or Google Form | QR to Sheets |
|---|---|---|
| Cost | Free | Free for one phone, one link and 300 scans in total; two links need the paid plan |
| Who does the work | Each volunteer writes or submits twice a shift | One coordinator phone or tablet scans a badge |
| Names that do not match | Common on paper; a dropdown helps | Rare, because the ID is read from the badge |
| Forgotten sign-outs | Common; flagged by the formulas above | Still happen; flagged by the same formulas |
| Signal drops | Paper is unaffected; a form fails to submit | Scans are kept on the phone and sent when the signal is back |
| Hours per shift and per month | The formulas in this guide | The same formulas in this guide |
| Where the record lives | A clipboard or your Google Sheet | Your Google Sheet |
| Method | Who records the time | Phones needed | Cost |
|---|---|---|---|
| Paper sign-in sheet, typed up later | The volunteer writes it | None | Free |
| Google Form on volunteers' own phones | The form, when the volunteer submits | Each volunteer's own phone | Free |
| Google Form on a tablet at the table | The form, one volunteer at a time | One shared tablet | Free |
| QR to Sheets, badge scanned on two links | The scan, at the moment the badge is read | One coordinator phone or tablet | 7 USD per scanning phone per month for two links |
Is this a time clock?
No. Everything here records presence: a badge or a name was presented at a time. A scan shows a badge was presented; it does not show who presented it. The hours are a record for your volunteer programme, useful for rotas, thank-yous, recognition and your own reporting. They are not a wage or time-clock record, and a spreadsheet that anyone with edit access can change is not a compliance record.
- If anyone on the rota is paid, even a stipend, their hours belong in your payroll process, not in this sheet.
- If an outside body, such as a school service-hours programme, asks for verified hours, follow that body's own sign-off process. The sheet can help you fill it in; it does not replace a signature.
- Keep the sheet in the programme's own Google account, shared only with the coordinators who need it. Scanning adds rows without giving the person at the table access to the file.
For headcounts at services, children's check-in and the wider picture for faith and community groups, the church and nonprofit attendance guide covers what this one does not, and after-school sign-in and sign-out uses the same two links for children's arrival and pickup.
Pricing, stated plainly. One phone, one scanner link and up to 300 scans in total cost nothing, which covers a trial of sign-in only. Sign-in and sign-out need two links, which means the paid plan at 7 USD per registered device per month: one coordinator phone or one tablet is 7 USD a month, however many volunteers it scans. The badges come from a free tool that needs no signup.
Step by step
- 1
Give every volunteer an ID on a Roster tab
One row per volunteer: a short ID such as V-014 in column A and the name in column B. The ID is what gets scanned or chosen; the name stays in the sheet.
- 2
Record a sign-in and a sign-out for every shift
Use a Google Form with a Sign in or Sign out question, a paper sheet typed up later, or two scanner links named Sign in and Sign out. Every row needs a time, an ID and a direction.
- 3
Pair each sign-in with its sign-out on a Calc tab
A MINIFS finds the same volunteer's next sign-out on the same day, and a status column marks each shift as OK or as something to check.
- 4
Total hours per volunteer per month
A Month tab lists every volunteer from the Roster, with SUMIFS for hours and COUNTIFS for shifts and for anything still to check in that month.
- 5
Confirm missed sign-outs with the volunteer
A shift with no sign-out counts as zero until someone asks the volunteer. Record the confirmed hours on a Corrections tab and leave the raw log untouched.
Frequently asked questions
How do I calculate volunteer hours in Google Sheets?+
Record a sign-in and a sign-out row for each shift. On a Calc tab, use MINIFS to find the same volunteer's next sign-out on the same day, subtract the sign-in time and multiply by 24 for decimal hours. A SUMIFS on a Month tab then totals the hours per volunteer for any month.
What should I do when a volunteer forgets to sign out?+
Flag it and ask. The formulas mark the shift as Check: no sign-out and count it as zero until someone confirms the time. Record the confirmed hours on a separate Corrections tab with who confirmed them, and never type an estimated time into the raw log.
Can volunteers scan with their own phones?+
Not on a scanner link. Each phone that scans counts as one device, so forty volunteers on their own phones would be forty devices. Use one coordinator phone or a tablet at the sign-in table to scan badges, or use a free Google Form if volunteers must use their own phones.
Why do my hours formulas not work on QR to Sheets rows?+
QR to Sheets writes the time as text in the form YYYY-MM-DD HH:MM:SS, and date comparisons and arithmetic need real date-times. Convert the time with VALUE(), as the Calc tab in this guide does. Google Forms timestamps are already real date-times and need no conversion.
Is a volunteer hours sheet a timesheet for paid staff?+
No. It records presence for your volunteer programme. It has no break rules, no approval step and no protection against editing, so anyone who is paid should be on your payroll process instead.
What does it cost to track sign-in and sign-out with QR to Sheets?+
Trying one link on one phone is free up to 300 scans in total. Sign-in and sign-out need two links, which is the paid plan at 7 USD a month for the one phone or tablet at the sign-in table. The badge maker is free.