Annual Leave Ledger

About this template

When to use it

A per-employee ledger of statutory annual leave (KR) — days accrued, days carried over, days taken and what is left — with the leave-use promotion flag falling out of the arithmetic. 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, Used (days) total, Used (days) average, Used (days) max. Under it, Summary by service band (rows 12–15) splits the same days across the four bands in column B, and Leave days used by month (rows 19–22) is hand-typed for Mar to Jun with a total in B23 and an average in B24.

Filling order

1. Rows 2–21 of Input data take No., Name / Affiliation, Start date, Service band, Leave accrued (days), Carried over (days), Used (days), Remaining (days), Use promotion (KR), Notes in columns A–J.
2. Keep Service band to Under 1 year, 1 year or more, 3 years or more, 5 years or more. These four strings are the COUNTIF criteria in B12:B15 — and note 8 of the Usage guide warns why they carry no tilde: a tilde is an escape character inside COUNTIF and would silently return 0.
3. Fill only three cells per person: Leave accrued, Carried over, Used. Columns H and I follow on their own.
4. Accrual follows the statute: 1 day per month worked in full during the first year, capped at 11; 15 days from one year of service; from three years, 1 further day for every additional two years, up to 25.
5. Column G must hold plain numbers. A half-day is 0.5 — the sample for Lee Su-min shows 4.5.

How the formulas calculate

• The key metrics read column G: =COUNT(‘Input data’!$G$2:$G$21), SUM, =IFERROR(AVERAGE(…),0), =IFERROR(MAX(…),0).
• Each band row pairs =COUNTIF(‘Input data’!$D$2:$D$21,$B12) with SUMIF and AVERAGEIF over column G, and a share of =IFERROR(C12/COUNT(‘Input data’!$G$2:$G$21),0).
• Remaining is =E2+F2-G2; accrued plus carried over, less taken.
• Use promotion is =IF(H2>=5,”Notice required”,”N/A”). Five days is the trigger written into the sheet, not into the law — the legal obligation is the written notice itself, due six months before the leave year ends.
• Under the monthly table, =SUM(C19:C22) and =AVERAGE(C19:C22) return 119 and 29.75.

Examples

• Row 2 — 1 · Kim Seo-yeon / Corporate Planning Team · 2019-03-04 · 5 years or more · 18 · 0 · 9.5 · 8.5 · Notice required.
• Row 9 — Jang Seo-woo has accrued 16 and used 16, so Remaining is 0 and the flag reads N/A.
• Four of the eight sample employees clear the five-day threshold; the eight rows total 67.0 days, average 8.4 and peak at 16.

Common mistakes

• Writing a band as a range with a tilde, which turns the COUNTIF row into 0 without any error showing.
• Typing over column H instead of correcting accrued, carried over or used.
• Recording a half-day as 1, which quietly overstates consumption across the year.
• Treating the flag as the notice. It marks who needs one; the written notice still has to be issued and filed.