21. Real-World Project Briefs

21.4 HR Attendance and Payroll Sheet

By Ravindra Bagale · Last updated: 6 October 2026

The ask. Ravina, the store lead, keeps attendance. At month end Shahrukh in HR needs present days, overtime pay and the payable amount for each person, rounded to the rupee. Salaries here are fictional practice numbers.

Sheet Payroll, A1:F6. Working days in the month = 26 (cell I1, named WorkDays). Fictional data:

What to deliver (one under the other):

1. Make the range a Table named tblPay and name cell I1 WorkDays.
2. Add Attendance % = Present days ÷ WorkDays.
3. Add Earned salary = Monthly salary ÷ WorkDays × Present days.
4. Add OT pay = OT hours × OT rate.
5. Add Payable = ROUND(Earned salary + OT pay, 0).
6. Total the Payable column and check it against the bank upload file.

Key formulas (row 2; copy down or let the Table fill them):

G2: =[@[Present days]]/WorkDays
H2: =[@[Monthly salary (₹)]]/WorkDays*[@[Present days]]
I2: =[@[OT hours]]*[@[OT rate (₹/hr)]]
J2: =ROUND([@[Earned salary]]+[@[OT pay]],0)

Expected result (check your numbers):

Total payable = ₹78,420.

Checks before you send it:

1. WorkDays is one named cell, not typed into every formula.
2. Rounding happens once, on the final Payable.
3. Present days never exceed WorkDays (add Data Validation).
4. This is a practice sheet. Real payroll also needs statutory deductions; ask your HR or CA team for the current rules.

Next step: turn this payroll sheet into a printable, one-page slip per employee with an employee drop-down and XLOOKUP. See 21.11 Salary Slip Creation.