What should I practice after reading this tutorial?

Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.

Which Excel version do I need for the formulas in this article?

Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.

Tutorials - 2026-01-21

Excel for HR and Payroll

Manage employee data, calculate payroll, and track HR metrics with Excel.

Excel for Human Resources

HR professionals rely on Excel for employee management, payroll processing, workforce analytics, and compliance reporting. This guide covers the essential formulas and techniques every HR professional should know. See Excel Use Cases for more industries.

Employee Data Management

Calculating Employee Tenure Track how long employees have been with your company: \\\ ' Years of service (whole years) =DATEDIF(HireDate, TODAY(), "Y") ' Years and months =DATEDIF(HireDate, TODAY(), "Y") & " years, " & DATEDIF(HireDate, TODAY(), "YM") & " months" ' Total months of service =DATEDIF(HireDate, TODAY(), "M") ' Days until work anniversary =DATE(YEAR(TODAY()), MONTH(HireDate), DAY(HireDate)) - TODAY() \\\ Age Calculations \\\ ' Current age =DATEDIF(BirthDate, TODAY(), "Y") ' Age as of specific date =DATEDIF(BirthDate, TargetDate, "Y") ' Retirement eligibility (age 65) =IF(DATEDIF(BirthDate, TODAY(), "Y")>=65, "Eligible", "Not yet") ' Years until retirement =MAX(0, 65 - DATEDIF(BirthDate, TODAY(), "Y")) \\\ Service Level Bands Categorize employees by tenure for benefits or recognition: \\\ ' Simple bands =IF(Tenure>=10, "10+ Years", IF(Tenure>=5, "5-10 Years", IF(Tenure>=1, "1-5 Years", "< 1 Year"))) ' Using IFS (cleaner) =IFS(Tenure>=20, "Veteran", Tenure>=10, "Senior", Tenure>=5, "Experienced", Tenure>=1, "Established", TRUE, "New") \\\

Payroll Calculations

Basic Pay Calculations \\\ ' Hourly to annual salary =HourlyRate * 40 * 52 ' Annual to hourly =AnnualSalary / 52 / 40 ' Monthly salary =AnnualSalary / 12 ' Bi-weekly pay =AnnualSalary / 26 \\\ Overtime Calculations \\\ ' Standard overtime (1.5x after 40 hours) =IF(Hours>40, (40*Rate) + ((Hours-40)*Rate*1.5), Hours*Rate) ' With double-time after 60 hours =IF(Hours>60, (40*Rate) + (20*Rate*1.5) + ((Hours-60)*Rate*2), IF(Hours>40, (40*Rate) + ((Hours-40)*Rate*1.5), Hours*Rate)) ' Weekly overtime summary =SUMIF(EmployeeCol, EmployeeName, OvertimeHoursCol) * Rate * 1.5 \\\ Tax and Deduction Calculations \\\ ' Simple tax bracket (example) =IF(GrossPay>5000, GrossPay*0.25, IF(GrossPay>3000, GrossPay*0.15, GrossPay*0.10)) ' Net pay calculation =GrossPay - FederalTax - StateTax - SocialSecurity - Medicare - HealthInsurance - 401k ' 401k contribution (percentage of gross) =GrossPay * ContributionPercent ' Employer 401k match (up to 6%) =MIN(EmployeeContribution, GrossPay * 0.06) \\\ Commission Calculations \\\ ' Flat commission rate =Sales * CommissionRate ' Tiered commission =IF(Sales>100000, Sales*0.10, IF(Sales>50000, Sales*0.07, Sales*0.05)) ' Commission with base salary =BaseSalary + (Sales * CommissionRate) ' Accelerator after quota =IF(Sales>Quota, (Quota*BaseRate) + ((Sales-Quota)*AcceleratedRate), Sales*BaseRate) \\\

Working Days and Time Off

Business Days Calculations \\\ ' Working days between dates (excludes weekends) =NETWORKDAYS(StartDate, EndDate) ' Working days excluding holidays =NETWORKDAYS(StartDate, EndDate, HolidayRange) ' Custom work week (Mon-Fri = 1, Mon-Sat = 2, etc.) =NETWORKDAYS.INTL(StartDate, EndDate, 1, HolidayRange) ' Add working days to a date =WORKDAY(StartDate, NumberOfDays, HolidayRange) ' Project end date (10 working days from start) =WORKDAY(ProjectStart, 10, Holidays) \\\ PTO and Leave Tracking \\\ ' PTO balance =PTOAllowance - PTOUsed ' PTO accrual (monthly) =AnnualPTO / 12 ' PTO accrual (per pay period, bi-weekly) =AnnualPTO / 26 ' Days until PTO resets =DATE(YEAR(TODAY())+1, 1, 1) - TODAY() ' Sick days remaining =SickDayAllowance - SUMIF(EmployeeCol, EmployeeName, SickDaysUsedCol) \\\ Attendance Tracking \\\ ' Attendance rate =DaysWorked / ScheduledDays * 100 ' Absence rate =(ScheduledDays - DaysWorked) / ScheduledDays * 100 ' Late arrivals count =COUNTIFS(EmployeeCol, EmployeeName, StatusCol, "Late") ' Perfect attendance flag =IF(AND(Absences=0, LateArrivals=0), "Perfect", "") \\\

HR Analytics and Reporting

Headcount Analysis \\\ ' Total headcount =COUNTA(EmployeeIDCol) ' Active employees =COUNTIF(StatusCol, "Active") ' Headcount by department =COUNTIFS(DepartmentCol, "Sales", StatusCol, "Active") ' Headcount by location =COUNTIFS(LocationCol, "New York", StatusCol, "Active") ' Full-time vs Part-time =COUNTIF(EmploymentTypeCol, "Full-Time") =COUNTIF(EmploymentTypeCol, "Part-Time") \\\ Turnover Calculations \\\ ' Monthly turnover rate =Terminations / AverageHeadcount * 100 ' Annual turnover rate =SUMIF(YearCol, 2024, TerminationsCol) / AverageAnnualHeadcount * 100 ' Voluntary vs Involuntary turnover =COUNTIFS(TermTypeCol, "Voluntary", YearCol, 2024) / AverageHeadcount * 100 ' Retention rate =100 - TurnoverRate \\\ Salary Analysis \\\ ' Average salary by department =AVERAGEIF(DepartmentCol, "Engineering", SalaryCol) ' Median salary =MEDIAN(SalaryRange) ' Salary range (min to max) =MIN(SalaryRange) & " - " & MAX(SalaryRange) ' Compa-ratio (salary vs midpoint) =ActualSalary / MidpointSalary ' Salary percentile =PERCENTRANK(SalaryRange, EmployeeSalary) \\\ Diversity Metrics \\\ ' Gender distribution =COUNTIF(GenderCol, "Female") / COUNTA(GenderCol) * 100 ' Department diversity =COUNTIFS(DepartmentCol, "Engineering", GenderCol, "Female") / COUNTIF(DepartmentCol, "Engineering") * 100 ' Age group distribution =COUNTIFS(AgeCol, ">=30", AgeCol, "<40") / COUNTA(AgeCol) * 100 \\\

Compliance and Reporting

FLSA Overtime Eligibility \\\ ' Check if exempt (simplified - actual rules are complex) =IF(AND(AnnualSalary>=35568, JobDuties="Exempt"), "Exempt", "Non-Exempt") ' Flag for overtime eligibility =IF(FLSAStatus="Non-Exempt", "Eligible for OT", "Exempt from OT") \\\ Benefits Eligibility \\\ ' Health insurance eligibility (30+ hours/week) =IF(WeeklyHours>=30, "Eligible", "Not Eligible") ' 401k eligibility (1 year service, 21+ years old) =IF(AND(TenureYears>=1, Age>=21), "Eligible", "Not Eligible") ' FMLA eligibility (12 months, 1250 hours) =IF(AND(TenureMonths>=12, HoursWorked>=1250), "Eligible", "Not Eligible") \\\ Audit Trail \\\ ' Last updated timestamp =TEXT(NOW(), "MM/DD/YYYY HH:MM") ' Change flag =IF(CurrentValue<>PreviousValue, "CHANGED", "") ' Data validation status =IF(AND(ISNUMBER(Salary), Salary>0, LEN(EmployeeID)=6), "Valid", "Check Data") \\\

Useful HR Formulas Summary

Task Formula Years of service =DATEDIF(HireDate, TODAY(), "Y") Employee age =DATEDIF(BirthDate, TODAY(), "Y") Working days =NETWORKDAYS(Start, End, Holidays) Overtime pay =IF(Hours>40, 40*Rate+(Hours-40)*Rate*1.5, Hours*Rate) Turnover rate =Terminations/AvgHeadcount*100 Headcount by dept =COUNTIFS(Dept, "Sales", Status, "Active") Average salary =AVERAGEIF(Dept, "Engineering", Salary) PTO balance =Allowance - Used

Related Resources

Excel Use Cases Date & Time Functions → DATEDIF Function → NETWORKDAYS Function → COUNTIFS Function → AVERAGEIF Function →

Apply this tutorial in your workbook

Copy one example from this article into a blank sheet. Change the sample ranges to match your column letters. Press F2 and Enter after edits so Excel recalculates. Compare your result to the expected output in the article. Related hubs: Excel functions directory · Formula guides · Function comparisons

Frequently asked questions

What should I practice after reading this tutorial? Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.
Which Excel version do I need for the formulas in this article? Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.