U Love Office
Home › Blog › Excel & Data
Excel & Data

How to create an employee attendance tracker and dashboard in Excel

✍️ admin 📅 20 November 2026

Quick answer: Build an attendance tracker in Excel with one row per employee and one column per day of the month. Enter a short code in each cell (for example P present, A absent, AL annual leave, SL sick leave, WFH work from home, H holiday). Use COUNTIF to total each code per employee, conditional formatting to colour codes, and NETWORKDAYS.INTL with weekend code 1 for the UAE Saturday–Sunday weekend to calculate working days. For late arrivals and overtime, keep a separate time log with clock-in and clock-out times and compare them with shift times. Summarise everything on a dashboard sheet.

Many small UAE businesses do not need expensive HR software for attendance. A well-built Excel tracker handles 5 to 100 employees comfortably, and it gives you the data you need for payroll, leave balances and conversations about punctuality.

Excel monthly attendance grid with status codes and a present-days count
One short code per cell keeps the grid readable and countable.

Step 1: Decide your attendance codes

Agree a short list of codes and do not let people invent new ones. A typical set:

Put the codes in a small table on a Settings sheet with a description for each. Then use Data Validation > List on the grid, pointing to the code list, so people can only choose valid codes from a drop-down. Our guide on creating a drop-down list in Excel shows how.

Step 2: Build the monthly grid

  1. On a sheet named for the month, put Employee in A1 and Department in B1.
  2. In C1, enter the first date of the month. In D1, enter =C1+1 and copy across for the rest of the month.
  3. Format the dates to show the day name and number: custom format ddd d.
  4. List employees in column A, one per row.

To grey out weekend columns automatically, select the grid and add a conditional formatting rule using a formula:

=WEEKDAY(C$1,2)>5

This marks Saturdays and Sundays (the UAE federal weekend). If your company works Saturdays, change the rule to =WEEKDAY(C$1,2)=7 for Sundays only.

Step 3: Colour the codes

Add conditional formatting rules so the grid is readable at a glance:

Use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, or “Equal to” for exact matches.

Step 4: Add summary columns

After the last day column, add summary columns for each employee:

Adjust AF to the last day column of the month. Keep a named holiday table as explained in our Excel date formulas guide.

Four summary metrics calculated for each employee in an attendance tracker
These four numbers answer most attendance questions.

Step 5: Track late arrivals and overtime with times

Codes tell you that someone was late, but not by how much. For that, keep a Time Log sheet with one row per employee per day:

Date Employee Shift start Clock in Shift end Clock out

Then add:

The UAE Labour Law sets normal working hours at up to eight per day and 48 per week, with overtime paid at a premium: generally 25% extra, and 50% extra for overtime between 10 pm and 4 am, subject to exceptions. Use our Overtime Pay Calculator to check amounts, and see our guide on how to calculate working hours in Excel for more time formulas.

If you use a biometric or card system, export its data monthly and clean it with Power Query instead of typing times. Our Power Query guide shows how.

Excel time log calculating minutes late and overtime hours
Times must be real Excel times, not text, for these formulas to work.

Step 6: Build a simple dashboard

Create a Dashboard sheet with:

Add a month selector with a drop-down if you keep each month on a separate sheet, and use INDIRECT or, better, combine monthly sheets into one table with Power Query and use a PivotTable with a slicer. Our guide on creating an Excel dashboard covers layout.

Bar chart of attendance percentage by department
A simple chart is enough for a monthly management review.

Linking attendance to leave and payroll

Privacy and fairness

Attendance data is personal data. Keep the workbook in a restricted folder, give managers access only to their own teams where possible, and use attendance data consistently for everyone. Before discussing lateness with an employee, check the data is accurate, especially if it came from a machine export.

Attendance analytics dashboard on a laptop screen

Monthly routine for the attendance owner

Give one person ownership of the tracker and a simple routine:

  1. Before the month starts: copy last month’s sheet, clear the codes, update the start date and check the holiday table includes any announced public holidays.
  2. Daily or weekly: managers or the receptionist enter codes for their teams. Late arrivals are marked the same day while memories are fresh.
  3. Month end: import machine times if you use a biometric system, reconcile codes against approved leave requests, and resolve any blanks.
  4. Payroll handover: send absent days, unpaid leave, sick leave and approved overtime to payroll in one summary.
  5. Lock the month: protect the sheet so codes cannot be changed after payroll.

The routine takes about an hour at month end for a 30-person company, and much less once managers are used to entering codes daily, and it removes most attendance disputes, because data is entered once, checked once and then fixed.

Flow of the monthly process for maintaining an attendance tracker
One owner, one routine, fewer disputes.

Worked example: spotting a pattern

In a 30-person trading company, the dashboard showed that Operations had 93% attendance in November, below the company average of 96%. Filtering the time log showed that most late arrivals in Operations were on Mondays and Thursdays, all between 9:10 and 9:25. A conversation revealed the shift start had been moved to 9:00 while a staff bus still arrived at 9:15. The company moved the Operations shift to 9:15 to 6:15. Late arrivals dropped by more than half the next month. Without the data, the issue would have been treated as individual lateness, and several good employees would have received warnings for a problem the company itself had created.

Common mistakes

📘 Continue reading

Frequently asked questions

Can I use Google Sheets instead?
Yes. COUNTIF, NETWORKDAYS.INTL, conditional formatting and data validation all work.

How do I handle half-days?
Use a code like HD and count it as 0.5 in your present formula: +0.5*COUNTIF(range,"HD").

What about Ramadan hours?
Reduced working hours apply in Ramadan. Change the expected hours in your time log for those dates, so overtime is calculated correctly.

How many employees can Excel handle?
Comfortably a few hundred with this design. Beyond that, consider HR software.

Key takeaways

Using attendance data in conversations

Numbers start a conversation; they do not end it. Before discussing attendance with an employee, check the data for errors, look for patterns such as the same day each week, and ask open questions about the cause. Transport problems, caring responsibilities or health issues are often behind repeated lateness. Agree clear expectations and any reasonable adjustment, record the conversation briefly, and follow your disciplinary policy only if the issue continues. Our guide to employee warning letters explains the formal steps.

Conclusion

A reliable attendance tracker needs only a few ingredients: fixed codes with a drop-down, a monthly grid with weekends shaded, COUNTIF summaries, NETWORKDAYS.INTL for working days, a time log for late and overtime minutes, and a simple dashboard. Lock each month after payroll, keep the file private, and you will have accurate data for payroll, leave and performance conversations.

Checklist for setting up an Excel employee attendance tracker
Set it up once and reuse it every month.

Last checked: 9 November 2026.