PeopleMuster

Attendance tracker (Excel template)

Attendance trackers in Excel go wrong in the arithmetic, not the typing. So this one does the sums for you: type the shift start, the check-in and the check-out, and the sheet works out late minutes and working hours on every row. A small summary block turns the month into an attendance rate and an on-time rate.

Use it for a team of up to a few dozen people whose times you collect by hand or from a device export. One row is one person on one date. For a printable roll with one mark per day, the attendance register is simpler; for a one-line-per-person month end, use the monthly attendance summary.

ATTENDANCE TRACKER
Company: [COMPANY NAME]
Month: [MONTH AND YEAR]
Expected working days this month: [EXPECTED DAYS]

SHEET 1 - DAILY LOG (row 1 is the header)
A Date | B Name | C Shift start | D Check in | E Check out | F Unpaid break (min) | G Late minutes | H Working hours | I Status | J Note
[DATE] | [EMPLOYEE NAME] | [SHIFT START] | [CHECK IN] | [CHECK OUT] | [BREAK MINUTES] | formula | formula | [STATUS] | [NOTE]
[DATE] | [EMPLOYEE NAME] | [SHIFT START] | [CHECK IN] | [CHECK OUT] | [BREAK MINUTES] | formula | formula | [STATUS] | [NOTE]
[DATE] | [EMPLOYEE NAME] | [SHIFT START] | [CHECK IN] | [CHECK OUT] | [BREAK MINUTES] | formula | formula | [STATUS] | [NOTE]

FORMULAS FOR ROW 2 (type once, then fill down)
G2 Late minutes:   =IF(D2="","",MAX(0,ROUND((D2-C2)*1440,0)))
H2 Working hours:  =IF(OR(D2="",E2=""),"",ROUND(MOD(E2-D2,1)*24-F2/60,2))

STATUS CODES (column I)
P = present, full day
PT = partial day (in, but short of the shift)
A = absent
L = on approved leave
H = company holiday

SHEET 2 - MONTHLY SUMMARY (one row per person; name in A2)
Name | Days present | Days absent | Leave days | Late arrivals | Late minutes | Hours worked | Attendance rate | On-time rate
[EMPLOYEE NAME] | =COUNTIFS(Sheet1!B:B,A2,Sheet1!I:I,"P")+COUNTIFS(Sheet1!B:B,A2,Sheet1!I:I,"PT") | =COUNTIFS(Sheet1!B:B,A2,Sheet1!I:I,"A") | =COUNTIFS(Sheet1!B:B,A2,Sheet1!I:I,"L") | =COUNTIFS(Sheet1!B:B,A2,Sheet1!G:G,">0") | =SUMIFS(Sheet1!G:G,Sheet1!B:B,A2) | =SUMIFS(Sheet1!H:H,Sheet1!B:B,A2) | =B2/[EXPECTED DAYS] | =1-E2/B2

What to put in each blank

[EXPECTED DAYS]
Count the working days in the month after weekly days off and company holidays. Type the number into the attendance rate formula too.
[SHIFT START]
Type the start of that person's own shift as a time, like 09:00. Late minutes are measured against it, never against one office-wide opening time.
[CHECK IN]
Type the first punch or sign-in of the day as a time. Leave it empty on an absent day and the formulas stay blank.
[CHECK OUT]
Type the last punch of the day. A check-out after midnight still works, because the formula wraps past 24:00.
[BREAK MINUTES]
Enter the unpaid break in minutes, or 0 when breaks are paid.
[STATUS]
Pick one code from the status list. Use PT when someone came in but left well short of the shift.

Before you send it

  • Format columns C, D and E as Time before you type into them. A time typed into a General cell turns into text, and every formula on that row returns an error.
  • To copy the layout, paste the header and rows into column A, then split on the | character. In Excel that's Data, Text to Columns, Delimited, Other. In Google Sheets it's Data, Split text to columns, Custom.
  • Keep one tab per month. A single tab that runs all year grows slow and makes the COUNTIFS ranges easy to break.
  • Lock the formula columns G and H once they work. Most broken trackers were broken by someone typing a number over a formula.
  • Read late minutes beside the status column. Ten minutes late on a partial day tells a different story from ten minutes late on a full one.

PeopleMuster fills the same columns from fingerprint punches or browser check-in: check-in, check-out, working hours and late minutes for each date, with on-time rate on the KPI card and a CSV export.

Questions people ask about this letter

How do I calculate late minutes in Excel?

Subtract the shift start from the check-in and multiply by 1,440, the number of minutes in a day. Wrap it in MAX(0, …) so an early arrival shows 0 instead of a negative number. Excel stores times as fractions of a day, which is why the multiplier works.

How is the attendance rate worked out?

Divide the days a person was present, full or partial, by the working days expected that month. Take holidays and approved leave out of the expected days first. Leave them in and a person on planned leave looks like a poor attender.

What does the on-time rate measure?

It is the share of check-ins with zero late minutes. A person present 20 days and late on 2 of them has an on-time rate of 90%.

Why does my working hours cell show a negative number?

The check-out is probably earlier than the check-in, which happens on a night shift. The MOD part of the formula fixes that by wrapping the time past midnight. Check that the formula in column H still includes it.

Other templates