Build a timesheet with automatic overtime rules

Northwind’s contractors bill regular and overtime hours at different rates, so the timesheet has to split every entry into the two buckets. The rule sounds simple — anything over eight hours in a day or forty in a week is overtime — but it is fiddly to apply by hand, because the daily and weekly limits interact: a long Monday can use up the week’s regular allowance early, pushing later days into overtime even if they were short.

This script does the split for you. It reads the raw timesheet, groups entries by person and week, then walks each week in date order, filling the regular bucket up to both limits before anything spills into overtime. It writes the two numbers back next to each entry, ready for billing.

What you’ll need

• A timesheet sheet with a header row and columns named person, date, hours, regular, and overtime. One row per person per day.
• The regular and overtime columns can start empty — the script fills them.
• hours and date stored as real numbers and dates, not text.

The script

// The spreadsheet holding the raw timesheet entries.
const TIMESHEET_SHEET_ID = '1abcTimesheetsId';

// Overtime thresholds. Hours beyond either limit count as overtime.
const DAILY_LIMIT = 8;   // regular hours allowed in a single day
const WEEKLY_LIMIT = 40; // regular hours allowed in a single week

/**
 * Splits every timesheet entry into regular and overtime hours,
 * applying both the daily and the weekly limit, and writes the
 * two figures back beside each row.
 */
function computeOvertime() {
  const sheet = SpreadsheetApp.openById(TIMESHEET_SHEET_ID).getSheets()[0];
  const [header, ...rows] = sheet.getDataRange().getValues();

  // Stop early if there is nothing but a header.
  if (!rows.length) {
    Logger.log('No timesheet entries — nothing to compute.');
    return;
  }

  // Map column names to their index so we can read by name.
  const col = Object.fromEntries(header.map((key, i) => [key, i]));

  // 1. Group entries by person and week, keeping the sheet row number.
  const grouped = new Map(); // "person|weekStart" -> entry list
  rows.forEach((r, i) => {
    const week = startOfWeek(r[col.date]);
    const key = `${r[col.person]}|${week.toISOString()}`;
    if (!grouped.has(key)) grouped.set(key, []);
    grouped.get(key).push({ rowIndex: i + 2, hours: r[col.hours] });
  });

  // 2. Work through each person-week, filling regular before overtime.
  for (const [, entries] of grouped) {
    let weeklyRegular = 0; // regular hours used so far this week

    for (const entry of entries) {
      // Cap today's regular hours at the daily limit.
      const regularToday = Math.min(entry.hours, DAILY_LIMIT);

      // ...and at whatever regular room is left in the week.
      const weeklyRoom = Math.max(0, WEEKLY_LIMIT - weeklyRegular);
      const regular = Math.min(regularToday, weeklyRoom);

      // Everything else on this entry is overtime.
      const overtime = entry.hours - regular;
      weeklyRegular += regular;

      // 3. Write the split back beside the entry.
      sheet.getRange(entry.rowIndex, col.regular + 1).setValue(regular);
      sheet.getRange(entry.rowIndex, col.overtime + 1).setValue(overtime);
    }
  }
  Logger.log('Overtime computed for ' + grouped.size + ' person-weeks.');
}

/**
 * Returns the Sunday-midnight start of the week containing `d`,
 * used as a stable key for grouping a week's entries.
 */
function startOfWeek(d) {
  const week = new Date(d);
  week.setDate(week.getDate() - week.getDay());
  week.setHours(0, 0, 0, 0);
  return week;
}

How it works

1. computeOvertime reads the whole timesheet and builds a col lookup so it can address columns by name rather than by position.
2. It groups the rows into a Map keyed by person plus the start of their week. startOfWeek snaps any date back to the Sunday midnight before it, so every entry in the same calendar week shares one key. Each grouped entry keeps its real sheet row number for writing back later.
3. For each person-week it processes the entries in turn, tracking weeklyRegular — the regular hours already used.
4. An entry’s regular hours are the smallest of three things: the hours worked, the DAILY_LIMIT, and the regular room left in the week. Whatever is left over becomes overtime.
5. The regular figure is added to the weekly tally, so once a week reaches forty regular hours every later entry flows entirely to overtime.
6. The two numbers are written back into the regular and overtime columns next to each entry.

Example run

Say one contractor logs this week (Sunday-start week, WEEKLY_LIMIT 40, DAILY_LIMIT 8):

date	hours	→ regular	→ overtime
Mon	10	8	2
Tue	9	8	1
Wed	8	8	0
Thu	8	8	0
Fri	7	7	0

By Friday the week has 39 regular hours, so all 7 of Friday’s hours stay regular. The daily limit catches Monday and Tuesday; the weekly limit would catch a sixth day entirely. Total: 39 regular, 3 overtime.

Trigger it

Run the split on a daily schedule so the timesheet is always current:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for computeOvertime, time-driven, day timer, late in the evening after the day’s entries are in.

Running daily means the regular and overtime columns are ready first thing for billing, without anyone running it by hand.

Watch out for

• Entries are processed in the order they appear in the sheet. If the rows for a person-week are out of date order, the weekly allowance fills in the wrong sequence. Keep the sheet sorted by person then date.
• The week starts on Sunday, because getDay() returns 0 for Sunday. If Northwind’s payroll week starts on Monday, adjust startOfWeek accordingly.
• The script overwrites the regular and overtime columns every run. Do not hand-edit those cells — your changes will be replaced.
• It assumes one row per person per day. Two entries for the same person and date are treated independently, so neither is capped at eight by itself — sum them into a single row first.
• Times are taken as plain hours. A date value carrying a time component still groups correctly, but make sure hours is a number, not a duration.