Decimal hours are easier for payroll math than text durations. Apps Script can compute them from Date cells.
timesheetHoursCalculation subtracts clockIn from clockOut, adds 24h when negative (overnight), subtracts breakMin/60, and rounds to 2 decimals.
Rows missing Date objects leave hours blank instead of inventing values.
Pair with overtime-alert for weekly thresholds.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Timesheets | A employee | Worker |
| Timesheets | B date | Work date |
| Timesheets | C clockIn | Date/time |
| Timesheets | D clockOut | Date/time |
| Timesheets | E breakMin | Break minutes |
| Timesheets | F hours | Output |
What this script does
Per-row hours from timestamps with overnight and break handling.
Prerequisites
Clock columns are true datetimes, not text.
- Spreadsheet timezone correct
- Break in minutes
- No overlapping segments in one row
Walkthrough
9:00–17:00 break 30 → 7.5 hours.
Edge cases
Text times fail instanceof Date — coerce with new Date(string) if needed.
- Overnight add 24h
- Negative after break → 0
How to test
23:00 to 07:00 next day with 0 break → 8.
Hardening for production
Validate out>in within 16h cap; flag anomalies.
Variations
Multiple segments per day on child rows summed by employee+date.
Full code: timesheetHoursCalculation()
Enter clock-in/out as datetimes, then run timesheetHoursCalculation() before payroll export.
/**
* Sum clock-in/out pairs into hours per timesheet row.
*/
const TS = "Timesheets";
// A employee, B date, C clockIn, D clockOut, E breakMin, F hours
function timesheetHoursCalculation() {
const sheet = SpreadsheetApp.getActive().getSheetByName(TS);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const inn = values[i][2];
const out = values[i][3];
const brk = Number(values[i][4]) || 0;
if (!(inn instanceof Date) || !(out instanceof Date)) {
values[i][5] = "";
continue;
}
let ms = out.getTime() - inn.getTime();
if (ms < 0) ms += 24 * 60 * 60 * 1000; // overnight
let hours = ms / 36e5 - brk / 60;
if (hours < 0) hours = 0;
values[i][5] = Math.round(hours * 100) / 100;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 14: Requires real Date cells.
- Line 19: Overnight shift correction.
- Line 20: Converts break minutes to hours.
- Line 22: Two-decimal hours.
- Line 15: Hours output column.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: timesheet hours
- 1Timezone set correctly
- 2Clock columns are datetimes
- 3Break minutes numeric
- 4Overnight policy confirmed
- 5Run before overtime alerts
- 6Spot-check overnight rows
Frequently asked questions
clockIn/out were not Date objects — check cell formats.
Store decimal hours for math; format separately if needed.
Sum break minutes into column E before running.
Replace round-to-cent hours with your jurisdiction's rounding.
Date subtraction uses absolute ms — usually correct across DST.
Protect column F; only allow C–E edits.
Pivot or QUERY sum of F by employee and week number.