Overtime Work Log

About this template

When to use it

An overtime ledger built around the Korean weekly ceiling: one line per approved instance, with a running weekly total per person and a limit check that flips by itself. Three sheets — Dashboard, Input data, Usage guide — and only Input data takes typing.

What the dashboard holds

Everything on the Dashboard is a formula. “Key metrics — summed automatically from the ‘Input data’ sheet” holds B7:E7 — Rows entered, Overtime (h) total, Overtime (h) average, Overtime (h) max. Under it, Summary by name (rows 12–16) breaks the same hours down across the five names in column B, and Overtime hours by week (rows 20–23) is hand-typed for Jun W1 to Jun W4 with a total in B24 and an average in B25.

Filling order

1. Rows 2–21 of Input data take No., Reason / Work covered, Week, Name, Work date, Overtime type, Overtime (h), Weekly total (h), Limit check, Approval in columns A–J; column A is pre-numbered 1–20.
2. Write the week the way the samples do — Jun W1 — and spell the name exactly as it appears on other rows. Note 5 of the Usage guide is blunt about this: the SUMIFS in column H matches both as text.
3. Column G must hold plain numbers. Holiday and night hours go in this one column; only the Overtime type in column F tells them apart.
4. Keep Overtime type to Weekday overtime, Night work, Holiday work.
5. Record the approver in column J — the samples use Team lead approved and Division head approved — and file the employee’s written consent with any row that goes over the limit.

How the formulas calculate

• The key metrics read column G: =COUNT(‘Input data’!$G$2:$G$21), SUM, =IFERROR(AVERAGE(…),0) and =IFERROR(MAX(…),0).
• Each name row pairs =COUNTIF(‘Input data’!$D$2:$D$21,$B12) with SUMIF and AVERAGEIF over column G, plus a share of =IFERROR(C12/COUNT(‘Input data’!$G$2:$G$21),0).
• Weekly total is =ROUND(SUMIFS($G$2:$G$21,$C$2:$C$21,$C2,$D$2:$D$21,$D2),1) — every row for the same week and the same person, added together and repeated on each of those rows.
• Limit check is =IF(H2>12,”Over limit”,”Within limit”). Twelve hours a week is the statutory ceiling on extended work in Korea (KR); to work to a stricter internal rule, change the 12 in every row of column I.
• Under the weekly table, =SUM(C20:C23) and =AVERAGE(C20:C23) return 201 and 50.25.

Examples

• Row 2 — 1 · Month-end close support · Jun W1 · Kim Seo-yeon · 2026-06-02 · Weekday overtime · 3.0 · Team lead approved.
• Park Ji-hoon books 5.0, 5.0 and 4.0 in Jun W3, so column H shows 14.0 on all three rows and column I turns to Over limit on each of them — the pattern the sheet exists to expose.
• The ten sample rows total 40.0 hours, average 4.0 and peak at 8.0.

Common mistakes

• Writing the same week two ways, so the SUMIFS never adds the rows together and nobody ever hits the limit.
• Splitting holiday hours into a column of their own — they belong in column G and count toward the same 12 hours.
• Typing over columns H and I, which are formulas on every row.
• Leaving Approval blank on an over-limit row, which is exactly the row an audit will ask about.