Attendance System with Status Counts & VBA Check-in

An attendance system records who was present, absent, or late each day and totals it per person. You build it in Excel and use Copilot and ChatGPT to write the COUNTIFS and a check-in form.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It logs daily attendance with a status and counts present, absent, and late days for each person.
• Where it is used: Schools, classes, and offices that track who showed up.
• What you learn: COUNTIFS, calendar layout, charts, and AI-assisted VBA.

Features

• Daily status entry (Present / Absent / Late)
• Count of each status per employee or student
• Monthly attendance percentage
• A chart of attendance trend
• AI-written VBA check-in form

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• A list of people with IDs
• Developer tab for the VBA form

Folder Structure

attendance-system.xlsx
│
├── Sheet: People     (EmpID, Name)
├── Sheet: Attendance (Date, EmpID, Status)
├── Sheet: Summary    (per-person counts and %)
└── Module: AttendMacros (VBA)

• People: the roster.
• Attendance: one row per person per day.
• Summary: totals and percentage.
• AttendMacros: check-in code.

Step-by-Step Development

Step 1 – Build the attendance log

What are we building?

A table named Att with Date, EmpID, Status.

Why do we need it?

Every attendance record lives here for counting.

How do we build it?

Make the table and add a dropdown for Status using Data > Data Validation with the list Present,Absent,Late.

=COUNTA(Att[EmpID])

• What it does: counts total attendance rows.
• Why needed: a size check on the log.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"How do I add a dropdown in Excel with the options Present, Absent, Late?"

Step 2 – Count each status with COUNTIFS

What are we building?

Columns for Present, Absent, and Late counts per person.

Why do we need it?

You need a total for each status to judge attendance.

How do we build it?

On Summary, link EmpID in A2.

=COUNTIFS(Att[EmpID], A2, Att[Status], "Present")
=COUNTIFS(Att[EmpID], A2, Att[Status], "Absent")
=COUNTIFS(Att[EmpID], A2, Att[Status], "Late")

• What it does: counts rows where both EmpID and Status match.
• Important line: two condition pairs (range, criteria) filter by person and status.
• Why needed: each status gets its own tally.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write COUNTIFS that counts rows where EmpID equals A2 and Status equals Present."

Step 3 – Attendance percentage

What are we building?

A percentage of present (and late counted as half) days.

Why do we need it?

A single number shows reliability.

How do we build it?

=IFERROR((Present+(Late*0.5))/Total_Days, 0)

• What it does: present days plus half the late days, divided by total days.
• Important line: IFERROR avoids a divide-by-zero when no days are logged.
• Why needed: turns counts into a clear rate.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel formula for attendance percent where late counts as half, with divide-by-zero protection."

Step 4 – VBA check-in form

What are we building?

A form to record today's status for a person.

Why do we need it?

Faster than typing into the table by hand.

How do we build it?

Insert a UserForm with EmpID, Date, and Status boxes plus a button.

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Set ws = Sheets("Attendance")
    Dim r As Long
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(r, 1).Value = txtDate.Value
    ws.Cells(r, 2).Value = txtID.Value
    ws.Cells(r, 3).Value = txtStatus.Value
    MsgBox "Saved"
End Sub

• What it does: appends date, ID, and status to the next blank row.
• Important line: txtStatus.Value takes the chosen status from the form.
• Why needed: one click logs attendance.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write VBA that adds a new row to an Attendance sheet with Date, EmpID, Status from a form."

Complete Source Code

Summary formulas

=COUNTIFS(Att[EmpID], A2, Att[Status], "Present")
=COUNTIFS(Att[EmpID], A2, Att[Status], "Absent")
=COUNTIFS(Att[EmpID], A2, Att[Status], "Late")
=IFERROR((Present+(Late*0.5))/Total_Days, 0)

VBA check-in

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Set ws = Sheets("Attendance")
    Dim r As Long
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(r, 1).Value = txtDate.Value
    ws.Cells(r, 2).Value = txtID.Value
    ws.Cells(r, 3).Value = txtStatus.Value
    MsgBox "Saved"
End Sub

AI prompts

ChatGPT: "Dropdown with Present, Absent, Late."
Copilot: "COUNTIFS EmpID A2 and Status Present."
ChatGPT: "Attendance percent, late as half, error handled."
ChatGPT: "VBA add row Date, EmpID, Status."

Running the Project

1. Open attendance-system.xlsx.
2. Enter daily rows in Attendance or use the check-in form.
3. Summary updates counts and percentage.
4. Expected output: each person shows Present/Absent/Late totals and a percent.

Testing

Test Case 1

Input: Emp E001 — Present, Present, Absent. Expected Output: Present 2, Absent 1, Late 0, percent 66.7%. Explanation: COUNTIFS separates statuses; percent divides by 3 days.

Test Case 2

Input: E001 — Present, Late. Expected Output: Percent (1 + 0.5)/2 = 75%. Explanation: Late counts as half a present day.

Test Case 3

Input: No rows for E002. Expected Output: Percent 0 (not an error). Explanation: IFERROR returns 0 when Total_Days is 0.

Common Errors

COUNTIFS returns 0

Problem: Count is blank. Reason: Status text does not match exactly (e.g., "present"). Solution: Use the dropdown so casing stays consistent.

Percent shows #DIV/0!

Problem: Error on a new person. Reason: Total days is zero. Solution: Wrap the formula in IFERROR as in Step 3.

Form writes wrong sheet

Problem: Data missing from Attendance. Reason: Sheet name in VBA differs. Solution: Match Sheets("Attendance") to your real sheet name.

Improvements

• Add a monthly calendar grid view
• Color-code absent days automatically
• Send late alerts with Power Automate
• Ask ChatGPT for low-attendance warnings
• Export monthly report to PDF

Challenge Exercises

1. Count only the last 7 days for each person.
2. Add a "Streak" formula for consecutive present days.
3. Use ChatGPT to write a VBA button that marks everyone Present by default.
4. Build a chart of attendance percent per person.
5. Connect ChatGPT to flag anyone below 60% attendance.