Present Days
=COUNTIF(B2:AF2,"P")
Attendance marks are in B2:AF2
Counts days marked present.
HR & Attendance ToolsHR Formula HR Formula HR Formula HR Formula HR Formula HR Formula
Salary & Attendance Formula Helper
Create formulas for attendance sheets, salary deduction, overtime payment, present days, absent days, age, and HR reporting. Pick a formula and copy it into your workbook.
Absent Days
=COUNTIF(B2:AF2,"A")
Attendance marks are in B2:AF2
Counts days marked absent.
Attendance Percentage
=COUNTIF(B2:AF2,"P")/COUNTA(B2:AF2)
Format result as percentage
Divides present days by total marked days.
Salary Deduction
=(MonthlySalary/WorkingDays)*AbsentDays
=B2/C2*D2
Calculates deduction based on absent days.
Overtime Payment
=MAX(TotalHours-StandardHours,0)*HourlyRate
=MAX(B2-C2,0)*D2
Pays only hours above standard hours.
Age from DOB
=DATEDIF(B2,TODAY(),"Y")
B2 = date of birth
Returns completed age in years.
Building better HR spreadsheets
Attendance formulas work best when your sheet uses consistent marks such as P for present and A for absent. Avoid mixing full words, symbols, and blank cells unless your formulas are designed for them. For payroll, keep monthly salary, working days, absent days, overtime hours, and hourly rate in separate columns so salary deduction and overtime calculations remain easy to audit.
Salary and attendance FAQ
How do HR teams use Excel formulas for attendance?
HR teams use COUNTIF, COUNTA, IF, DATEDIF, and arithmetic formulas to count present days, absent days, attendance percentage, overtime, salary deduction, age, and experience.
How do I calculate attendance percentage?
Divide present days by total marked working days and format the result as a percentage.
Can I copy these formulas into Google Sheets?
Yes, these common HR formulas generally work in Microsoft Excel and Google Sheets.
Related Excel tools
Formula Tools
Excel Formula Finder
Search business problems like GST, profit margin, attendance, age, commission, and get the right Excel formula.
Error FixingFormula Error Fixer
Understand #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, and #NULL! errors with practical fixes.
DatesDate Formula Helper
Generate formulas for age, days between dates, month names, quarters, financial year, and end of month.