Attendance tracker in Excel: a monthly attendance sheet with totals

In short: one row per person, one column per day of the month, a code in each cell: P present, L late, H half day, A absent, S sick, V vacation. =COUNTIF($C6:$AG6,"P") counts each code, the rate divides the present days by the days marked, and NETWORKDAYS gives the workdays in the month without weekends and holidays.

Before you start: COUNTIF and percentages. NETWORKDAYS and COUNTA are explained in the exercises.

Try it now

The heart of every attendance tracker is a count: how many days was each person present?

Task In L2, count the days the person was present (code P). The formula is filled down to L6. Codes as in the template: P present, A absent, L late, S sick.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	4	5	6	7	8	11	12	13	14	15	Present
2	Olivia	P	P	P	A	P	P	L	P	P	P
3	James	P	L	P	P	P	S	S	P	P	P
4	Emma	P	P	P	P	P	P	P	P	P	P
5	Noah	A	P	P	P	L	P	P	A	P	P
6	Ava	P	P	S	S	S	P	P	P	L	P

Download the attendance tracker template

The file covers one month for up to 30 people with dates that fill themselves, weekends and US or UK holidays shaded grey, a drop-down for the codes, totals per code, the attendance rate with a red flag below your threshold, a sample month and an instructions sheet.

How the attendance sheet is laid out

Cell or column	Contents	Input or formula
B2	Month	Any date in the month, such as 1 Jan 2027
B3	Holidays	US, UK or None
C5 to AG5	Days 1 to 31	=DATE(YEAR($B$2),MONTH($B$2),1), then the day before +1 while still in the month
A, B	Name, group	Inputs, such as a class, team or shift
C to AG	Code per day	P, L, H, A, S or V from the drop-down
AH to AM	Totals per code	=COUNTIF($C6:$AG6,"P") and so on
AN	Days marked	=COUNTA($C6:$AG6)
AO	Attendance	=(AH6+AI6+0.5*AJ6)/AN6

Row 4 repeats each date with the format ddd, so you see Mon, Tue and so on above the day numbers. February simply ends after the 28th or 29th, because each day only follows on while it is still in the same month. A drop-down list in every day cell allows only the six codes, which keeps the counts reliable.

Totals and attendance rate

COUNTIF counts each code in the row. For the rate you need two numbers: the days that count as present and the days that were recorded at all. COUNTA counts every cell that is not empty, so days without an entry, such as weekends, stay out of the rate.

Task In L2, calculate the attendance rate: days with P or L (late counts as present) divided by all days that have a code. Empty cells are days without an entry. The formula is filled down to L6.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	4	5	6	7	8	11	12	13	14	15	Rate
2	Olivia	P	P	P	A	P		L	P	P	P
3	James	P	L	P	P	P	S	S	P
4	Emma	P	P	P	P	P	P	P	P	P	P
5	Noah	A	P	P	P	L	P	P	A	P	P
6	Ava	P	P	S	S	S	P	P		L	P

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

In the template late counts as present and a half day as half, which is what AH6+AI6+0.5*AJ6 does. If late should count as absent in your school or company, take AI6 out of the formula. The result is formatted as a percentage, and a conditional formatting rule turns it red when it falls below the threshold in AO3, preset to 90%.

Workdays in the month with NETWORKDAYS

To see whether every workday has an entry, you need the number of workdays in the month. NETWORKDAYS counts Monday to Friday between two dates and leaves out the dates in a holiday list.

Task In B3, calculate the workdays in the month in B1: from the first to the last day of the month, without weekends and without the holidays in E2:E3.

	A	B	C	D	E
1	Month	1 Jan 2027		Holiday	Date
2				New Year's Day	1 Jan 2027
3	Workdays			Martin Luther King Jr. Day	18 Jan 2027

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

In the template AO2 holds =NETWORKDAYS(C5,EOMONTH($B$2,0),Holidays). Holidays is a name for a column on the Holidays sheet that contains only the dates for the choice in B3: US federal holidays on their observed day, or bank holidays in England and Wales. The same rule that greys out weekends uses COUNTIF(Holidays,C$5)>0 for holidays. More date counting is in the lesson on DATEDIF and NETWORKDAYS.

Attendance sheet for school or for staff

• Classroom attendance: enter one class per sheet, use L for tardy and add your school's own codes on the drop-down if needed. The page on Excel for teachers has more ideas for class lists and grades.
• Employee attendance: use the group column for the team or location. For planned time off over the whole year, the PTO tracker fits better, and the work schedule shows who is due to work.
• Several months: right-click the sheet tab, Move or Copy, Create a copy, then change the date in B2 and clear the codes.

Attendance records are personal data. Keep the file somewhere safe and share it only with the people who need it.

Common mistakes

Codes in lowercase or with spaces

COUNTIF ignores case, so p and P both count. A trailing space does not match, though. The drop-down prevents it.

Dividing by the days in the month

Dividing by 31 makes everyone look absent on weekends. Divide by the days marked, or by the workdays from NETWORKDAYS if every workday must have an entry.

Wrong holidays

Scotland and Northern Ireland have different bank holidays from England and Wales. Add your own days on the Holidays sheet in the yellow rows.

Attendance formulas in German Excel

English	German
COUNTIF, COUNTA	ZÄHLENWENN, ANZAHL2
NETWORKDAYS, EOMONTH	NETTOARBEITSTAGE, MONATSENDE
DATE, YEAR, MONTH	DATUM, JAHR, MONAT

Which Excel versions support this?

COUNTIF, COUNTA, NETWORKDAYS, EOMONTH, drop-down lists and conditional formatting work in all current versions of Excel, including Excel for Mac and Excel for the web.

FAQ

More files are on the Excel templates page.