How to track employee sick days in a spreadsheet (free template)

Updated October 2026 · About 8 minutes to read

If you give employees separate sick time, you need to know how much each person has earned, how much they've used, what carries over and who is about to run out. This guide builds that in Google Sheets or Excel: a sick balance kept apart from vacation, sick time earned per hour worked, yearly caps, carryover, half days, a per-employee summary and a warning color for low balances, with a worked example at the end.

Formulas use commas between arguments (US English default). If your spreadsheet uses semicolons as the list separator (common in Europe), swap them. Every formula on this page was checked in a test workbook; the example numbers below are the results it produced.

Everything is in hours, because sick time earned per hour worked rarely comes out in whole days. Five sheets: Settings, Hours (hours worked per pay period), Leave (one row per absence), Employees (balances) and Summary.

Step 1: Settings

Keep the policy numbers in one place. The values below are an example policy (1 hour of sick time per 30 hours worked, earn and use up to 40 hours a year, carry up to 40 hours over); replace them with your own.

Cell	What goes in it	Example
B1	Year start	2026-01-01
B2	Year end	`=EDATE(B1,12)-1`
B3	Hours worked per 1 hour of sick time	30
B4	Most sick hours earned per year	40
B5	Most sick hours used per year	40
B6	Carryover cap (hours)	40
B7	Hours in a workday	8
B8	Low balance warning (hours)	8
D2:D20	Company holiday dates	2026-01-01, 2026-05-25, ...

Name D2:D20 Holidays and B8 LowBalance: in Excel, type the name in the Name Box left of the formula bar; in Google Sheets, use Data > Named ranges.

Step 2: Hours worked

On a sheet named Hours, add headers Employee, Period end and Hours worked in A1:C1, and add one row per person per pay period, copied from payroll. On the Employees sheet (Step 4), D2 adds up this year's hours:

=SUMIFS(Hours!$C:$C,Hours!$A:$A,A2,Hours!$B:$B,">="&Settings!$B$1,Hours!$B:$B,"<="&Settings!$B$2)

Rows outside the year in Settings are ignored, so last year's rows can stay.

Step 3: The leave log, with half days

On a sheet named Leave, use these headers in A1:H1: Employee, Type, First day, Last day, Half day, Override hours, Hours, Status. Sick days and vacation share the sheet; Type keeps them apart. In G2, copied down:

=IF(OR(C2="",D2=""),"",IF(F2<>"",F2,(NETWORKDAYS(C2,D2,Holidays)-IF(E2="Yes",0.5,0))*Settings!$B$7))

NETWORKDAYS counts Monday to Friday between the dates, including both, and skips holidays. Half day = Yes takes half a day off, so a one-day absence becomes 4 hours. Use Override hours for anything else: a 2-hour doctor's appointment, or a part-timer whose shifts aren't 8 hours.

Add dropdowns (Data > Data Validation, List, in Excel; Data > Data validation, Dropdown, in Sheets): names from Employees!$A$2:$A$101, Type Sick,Vacation,Unpaid, Half day Yes,No, Status Pending,Approved,Denied. A typo like "Sick day" wouldn't be counted.

Keep medical details out of the log; dates and hours are enough. Share the file only with whoever manages time off.

Step 4: Separate sick and vacation balances

On the Employees sheet, use headers A1:K1: Name, Vacation hours per year, Unused sick last year, Hours worked, Sick carryover, Sick earned, Sick used, Sick balance, Sick usable now, Vacation used, Vacation left. You fill in A to C; the rest are row-2 formulas, copied down.

E2, last year's unused sick time, limited to the carryover cap:

=MIN(C2,Settings!$B$6)

F2, sick time earned: hours worked ÷ 30, capped at the yearly maximum. ROUNDDOWN never rounds up unearned time:

=MIN(Settings!$B$4,ROUNDDOWN(D2/Settings!$B$3,2))

If your policy only credits whole hours, use INT instead:

=MIN(Settings!$B$4,INT(D2/Settings!$B$3))

G2, approved sick hours with a first day in this year:

=SUMIFS(Leave!$G:$G,Leave!$A:$A,A2,Leave!$B:$B,"Sick",Leave!$H:$H,"Approved",Leave!$C:$C,">="&Settings!$B$1,Leave!$C:$C,"<="&Settings!$B$2)

SUMIFS adds Hours only for rows matching every condition: this person, Sick, Approved, first day in this year. Pending and denied rows don't count.

H2, the sick balance:

=E2+F2-G2

If your policy also limits how much sick time can be used per year, I2 shows what's usable now: the balance, up to what's left under the use cap, never below zero:

=MAX(0,MIN(H2,Settings!$B$5-G2))

Vacation stays separate. J2 is the same total with "Vacation":

=SUMIFS(Leave!$G:$G,Leave!$A:$A,A2,Leave!$B:$B,"Vacation",Leave!$H:$H,"Approved",Leave!$C:$C,">="&Settings!$B$1,Leave!$C:$C,"<="&Settings!$B$2)

K2, vacation left:

=B2-J2

Vacation is front-loaded here to keep the focus on sick time. If it accrues, see How to calculate PTO accrual.

Step 5: Flag low balances with conditional formatting

Select A2:K101 on the Employees sheet. In Excel, open Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Add a red fill for negative balances:

=$H2<0

Then an amber fill for anyone under the warning level in Settings!B8:

=AND($A2<>"",$H2<LowBalance)

Negative balances also match the amber rule, so keep red on top in Manage Rules. $A2<>"" keeps empty rows plain.

In Google Sheets, use Format > Conditional formatting > Custom formula is. Sheets' conditional formatting can't reference another sheet directly, so use INDIRECT:

=AND($A2<>"",$H2<INDIRECT("Settings!B8"))

Step 6: Sick days used this year, per employee

On a Summary sheet, put names in A2 and down, with headers Sick hours, Sick days, Sick absences and Half-day absences in B1:E1.

B2, sick hours used:

=SUMIFS(Leave!$G:$G,Leave!$A:$A,$A2,Leave!$B:$B,"Sick",Leave!$H:$H,"Approved",Leave!$C:$C,">="&Settings!$B$1,Leave!$C:$C,"<="&Settings!$B$2)

C2, the same in days:

=ROUND(B2/Settings!$B$7,2)

D2, separate sick absences. COUNTIFS counts rows, so a three-day absence on one row counts once:

=COUNTIFS(Leave!$A:$A,$A2,Leave!$B:$B,"Sick",Leave!$H:$H,"Approved",Leave!$C:$C,">="&Settings!$B$1,Leave!$C:$C,"<="&Settings!$B$2)

E2, how many of those were half days:

=COUNTIFS(Leave!$A:$A,$A2,Leave!$B:$B,"Sick",Leave!$H:$H,"Approved",Leave!$E:$E,"Yes",Leave!$C:$C,">="&Settings!$B$1,Leave!$C:$C,"<="&Settings!$B$2)

For a team total, add =SUM(B2:B4) under the last name (adjust the range to your list).

For a monthly view, put the first formula in G1 and the second in H1, copy H1 across to R1, and give row 1 the custom number format mmm.

=Settings!$B$1

=EDATE(G1,1)

G2, copied across and down:

=SUMIFS(Leave!$G:$G,Leave!$A:$A,$A2,Leave!$B:$B,"Sick",Leave!$H:$H,"Approved",Leave!$C:$C,">="&G$1,Leave!$C:$C,"<="&EOMONTH(G$1,0))

An absence counts in the month of its first day; split one that crosses months into two rows if you need exact monthly totals.

Step 7: Carryover at year end

At year end, this gives what each person brings forward, up to the cap and never below zero:

=MIN(MAX(0,H2),Settings!$B$6)

Save a copy of the old year. In the new copy, paste the results into "Unused sick last year" as values only, change Settings!B1 and clear the old leave rows. No carryover? Set B6 to 0.

Worked example

Example settings, hours entered through September 30, 2026, July 3 a holiday:

Maria, full-time: 1,544 hours worked, 52 unused sick hours from 2025, 80 vacation hours a year.
Sam, part-time with 5-hour shifts: 690 hours worked, 6 unused sick hours, 40 vacation hours.
Jordan, hired August 3, 2026: 365 hours worked, 40 vacation hours.

Employee	Type	Dates	Half day	Override	Status	Hours
Maria	Sick	Mar 9–10			Approved	16
Maria	Sick	Jun 12	Yes		Approved	4
Maria	Sick	Oct 5		2	Approved	2
Maria	Vacation	Jul 1–6			Approved	24
Maria	Vacation	Sep 14–18			Pending	40
Sam	Sick	Feb 3		5	Approved	5
Sam	Sick	Apr 14–15		10	Approved	10
Sam	Vacation	Aug 10–11		10	Approved	10
Jordan	Sick	Sep 21			Approved	8
Jordan	Sick	Oct 13	Yes		Approved	4

July 1–6 is 24 hours (3 workdays) because July 3 is a holiday and July 4–5 is a weekend. A sick row dated December 2025 is ignored because it's outside the year.

Employee	Carryover	Earned	Sick used	Sick balance	Usable now	Vacation left
Maria	40	40	22	58	18	56
Sam	6	23	15	14	14	30
Jordan	0	12.16	12	0.16 (amber)	0.16	40

Maria carries over 40 of her 52 hours, and her earned time (1,544 ÷ 30, about 51.5) is capped at 40. Her balance is 58, but she has used 22 of the 40 hours allowed this year, so 18 are usable. Her pending vacation doesn't count until approved. Jordan's 365 ÷ 30 rounds down to 12.16 (12 with INT); his 0.16-hour balance is under the warning level, so his row turns amber. If the year ended today, Maria would carry 40, Sam 14 and Jordan 0.16.

Employee	Sick hours	Sick days	Sick absences	Half-day absences
Maria	22	2.75	3	1
Sam	15	1.88	2	0
Jordan	12	1.5	2	1

The team total is 49 hours. Maria's monthly row shows 16 in March, 4 in June and 2 in October. For part-timers like Sam, hours and absences say more than days.

Paid sick leave rules vary

Not legal advice. Paid sick leave rules differ by state, and some cities and counties have their own. Depending on where your employees work, there may be rules on how sick time is earned, used and carried over, and what records you keep. The 1-hour-per-30-hours policy here is an example to show the math, not a legal requirement or a recommendation. Check the rules wherever you have employees and set Settings to match.

Want the vacation side already built?

Simple PTO Tracker is a ready-made Google Sheets / Excel file for tracking time off in days. Sick is one of its leave types and can be set not to count against PTO, so sick days are logged and totaled in their own column while PTO balances, carryover caps and the team calendar are handled for you. It doesn't accrue sick time per hour worked; for that, use the formulas above. The free Lite version handles up to 5 employees; the full version ($14) handles up to 100.