Employee Attendance Management System in Excel: Monthly Codes, Leave Balance, Late Marks and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: employee-attendance-management-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Attendance, Late log, Leave, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Bagale Engineering Works, October 2026 (made-up data). Eight employees in four departments: Production, Accounts, Sales and Stores. Sundays are weekly offs and 2 October is a holiday, so each person has 26 working days in the month.

What the owner wants to know on the last day of the month:

The average attendance comes out at 91.8%, with 2 employees below 90%. If you only need a simple tick-and-cross sheet, start with the attendance sheet with icons; the brief in 21.4 HR attendance and payroll shows the one-sheet version. This page builds the full system.

2. Build it sheet by sheet

Step 1. Setup sheet: the month, the shift and the lists

1. Rename the first sheet Setup. In B3 type the first day of the month (01-10-2026), in B4 the shift start (09:30) and in B5 the grace minutes (10).
2. In A7:E7 type the headers Emp ID, Name, Department, Designation, Leave balance at month start and fill 8 employees in rows 8 to 15.
3. Type the departments in H8:H11 and the attendance codes in J8:J13 (P, A, HL, L, WO, H) with their meanings in column K.
4. Select C8:C40 > Data > Data Validation > List and point it at =$H$8:$H$11 (drop-down lists). Every other sheet reads names and codes from here, so a new employee is added in one place.

Step 2. Attendance sheet: a 31-day grid

1. Add a sheet Attendance. In D4 type =Setup!$B$3+0, in E4 =Setup!$B$3+1, and so on to AH4 (+30). Format the row as dd so it shows the day number.
2. In D5 type =TEXT(D4,"ddd") and copy it across: Thu, Fri, Sat... Change Setup B3 next month and both rows update.
3. In A6 type =Setup!A8 and in B6 =XLOOKUP(A6,Setup!$A$8:$A$15,Setup!$B$8:$B$15); do the same for the department in C6 (XLOOKUP). Copy down to row 13.
4. Select D6:AH23 > Data Validation > List > =Setup!$J$8:$J$13. Now only the six codes can be typed.
5. Add conditional formatting on the grid: cell value equal to "A" red, "L" amber, "WO" and "H" grey (highlight rules). Type the codes for the month.

Step 3. Attendance sheet: count the codes

1. In AI6 (Present) type =COUNTIF(D6:AH6,"P")+COUNTIF(D6:AH6,"HL")/2: a half day counts as half present. Ravindra Bagale: 25.
2. In AK6 (Absent) type =COUNTIF(D6:AH6,"A")+AJ6/2, where AJ6 counts the half days.
3. In AM6 count offs and holidays: =COUNTIF(D6:AH6,"WO")+COUNTIF(D6:AH6,"H"): 5.
4. In AN6 (Payable days) type =AI6+AL6+AM6: present + paid leave + offs.
5. In AO6 (Working days) type =DAY(EOMONTH(Setup!$B$3,0))-AM6: 31 − 5 = 26, and in AP6 =AI6/AO6 for the attendance %: 96.2%.
6. In row 14 count who was present each day: =COUNTIF(D6:D13,"P")+COUNTIF(D6:D13,"HL")/2 and copy across.

Step 4. Late log: minutes late and the grace rule

1. Add a sheet Late log with Date, Emp ID, Name, In time, Minutes late, Late? in row 4.
2. In E5 type =MAX(0,ROUND((D5-Setup!$B$4)*1440,0)). Times are fractions of a day, so ×1440 turns them into minutes (time calculations). 09:52 − 09:30 = 22 minutes.
3. In F5 type =IF(E5>Setup!$B$5,"Late","On time"). 09:35 is within the 10 grace minutes, so it is On time.
4. Back on Attendance, in AQ6 count late marks: =COUNTIFS('Late log'!$B$5:$B$60,A6,'Late log'!$F$5:$F$60,"Late"), in AR6 the example rule every 3 late marks cut half a day: =INT(AQ6/3)*0.5, and in AS6 the final payable days =AN6-AR6. Shahrukh: 27.5 − 0.5 = 27.

Step 5. Leave and Report sheets

1. On a sheet Leave, bring the opening balance with =XLOOKUP(A5,Setup!$A$8:$A$15,Setup!$E$8:$E$15) and leave taken with =XLOOKUP(A5,Attendance!$A$6:$A$13,Attendance!$AL$6:$AL$13).
2. Closing balance E5 =C5-D5 and status F5 =IF(E5<=1,"Low","OK"); highlight Low in amber.
3. On a sheet Report, list the departments in A5:A8 and type in B5 =COUNTIF(Attendance!$C$6:$C$13,A5), in C5 =SUMIFS(Attendance!$AI$6:$AI$13,Attendance!$C$6:$C$13,A5) (SUMIFS) and in F5 =AVERAGEIFS(Attendance!$AP$6:$AP$13,Attendance!$C$6:$C$13,A5) (AVERAGEIFS).

Step 6. Dashboard sheet: KPI cards and charts

1. Add a sheet Dashboard. Each KPI card is a blue label cell with a big formula cell under it (KPI cards).
2. Average attendance B5: =AVERAGE(Attendance!$AP$6:$AP$13) (91.8%). Below 90% D5: =COUNTIF(Attendance!$AP$6:$AP$13,"<0.9") (2). Low leave F5: =COUNTIF(Leave!F5:F12,"Low").
3. Insert a column chart of names (Attendance B6:B13) against attendance % (AP6:AP13), and a second chart of absent days by department from the Report sheet.
4. Keep the dashboard free of typing: it only reads. That is the rule from structuring a workbook.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Typing "p" or "Present" instead of the code. COUNTIF looks for exactly P. The drop-down on the grid stops other text; if you paste data in, check row 14 headcounts look sensible.
2. Counting weekly offs as working days. Attendance % should divide by working days (26 here), not 31, or everyone looks worse than they are.
3. Forgetting half days. A half day is half present and half absent. Count HL in both columns, divided by 2, so the days still add up to 31.
4. Hard-coding the month. Typing dates by hand in row 4 breaks next month. Build them from Setup B3 so one change rolls the whole sheet.
5. Mixing payroll rules into the grid. Keep rules like the late cut in their own columns. Then the grid stays a plain record that everyone agrees on.

5. More real examples

Example 1: Shift-based factory. Add a Shift column in Setup (A, B, C) and a shift start per shift; the Late log looks up the right start time with XLOOKUP instead of one fixed 09:30.

Example 2: Office with flexible hours. Replace the late rule with hours worked: log in and out times, and count a day as HL when hours are below 4.5.

Example 3: Retail shop with 2 branches. Add a Branch column next to Department and a second report block with SUMIFS by branch; the dashboard gets a branch drop-down.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate attendance percentage in Excel?

Divide present days by working days: =AI6/AO6, where working days are the days in the month minus weekly offs and holidays. Format as %. Ravindra Bagale: 25 ÷ 26 = 96.2%.

How do I count half days in an attendance sheet?

Give them their own code (HL) and add half of them to both present and absent: =COUNTIF(D6:AH6,"P")+COUNTIF(D6:AH6,"HL")/2.

How can Excel calculate late coming minutes?

Subtract the shift start from the in-time and multiply by 1440 (minutes in a day): =MAX(0,ROUND((D5-Setup!$B$4)*1440,0)). Compare with the grace minutes to mark Late.

Can this sheet calculate salary too?

It gives the final payable days. The salary slip generator and payroll calculator on the next pages take those days and work out pay.

How do I use it next month?

Change Setup B3 to the next month's first day, clear the codes and the Late log, and update the leave balances in Setup column E. Formulas and formatting stay.

7. Quick quiz

Ten questions on this page. Sign in to save your score.