TemplateHub
Excel Guides

Excel Attendance Sheet for a 12-Person Shop (Codes, COUNTIF, Payable Days)

By Arshad Ansari, software engineer. About the editor

Build a month's attendance grid for a small shop: P/A/L/H codes, COUNTIF payable days, late remarks, and how that number reaches the salary slip.

A shop with 12 people does not need an HR app to know who came to work. It needs a grid that a supervisor can fill in two minutes at closing, and a payable-days number that matches the salary slip. This is the layout I recommend. A downloadable TemplateHub attendance file will follow the same rules when it ships; you can build it today in a blank workbook.

Layout

  • Rows 1–3: business name, month (e.g. September 2026), working days in month (26).
  • Column A: employee code.
  • Column B: name.
  • Columns C onward: one column per calendar date (1–30/31).
  • Last three columns: count of P, count of H, payable days.

Freeze column B so names stay visible when you scroll to the 28th.

Codes (data validation only)

| Code | Meaning | |---|---| | P | Present, full day | | A | Absent, unpaid | | L | Paid leave (casual/earned as per your policy) | | H | Half day | | WO | Weekly off | | PH | Paid public holiday |

Do not allow "late" as a code. Late is a remark. If you mark late as A, you will underpay people who worked seven hours.

Formulas

For employee row 5, dates in C5:AG5:

  • Present days: =COUNTIF(C5:AG5,"P")
  • Half days: =COUNTIF(C5:AG5,"H")
  • Payable days: =COUNTIF(C5:AG5,"P")+COUNTIF(C5:AG5,"L")+COUNTIF(C5:AG5,"PH")+COUNTIF(C5:AG5,"WO")+0.5*COUNTIF(C5:AG5,"H")

Adjust the payable formula to your policy. Many shops do not pay weekly offs as payable days; they treat WO as a calendar marker only. If WO is unpaid as a "day", remove it from payable days and keep it for rostering.

Example: Priya has 22 P, 2 H, 2 WO, 2 A. If WO counts as paid rest: payable = 22 + 1 + 2 = 25. If WO does not count: payable = 22 + 1 = 23. Write the policy in row 2 of the sheet so the formula and the owner agree.

Joining mid-month

Do not COUNTIF the whole month. Either leave pre-joining dates blank (and do not use a default P) or start the COUNTIF range at the joining date. A blank is better than a WO you invented.

Late arrivals

Column Remarks or a second grid: date, employee, time in. Do not overwrite P. Payroll can apply a late deduction as a line on the salary slip, not by turning a present day into a half day unless that is written policy.

Month-end

  1. Lock the sheet (or export PDF) on the 1st of the next month.
  2. Copy payable days onto each salary slip.
  3. Keep the file for the period your state's Shops and Establishments rules require.

What this sheet is not

It is not a biometric system. It is not a leave-encashment register (put opening/closing leave on another tab). It is not a Form 16. Keep PAN and Aadhaar off this printout.

FAQ

Can I use Google Sheets on a phone at the counter?

Yes. Data validation still works. Give the supervisor edit access to one tab only.

How do I handle outdoor duty?

Add code OD and decide whether OD is payable like P. Document it in the header. Do not reuse P for outdoor — you will lose the audit trail.

More HR practice notes

See the HR templates category for leave-balance structure and why attendance should stay separate from identity documents.