Excel for HR and people operations

Q: How do I calculate years of service in Excel?

Use DATEDIF with "y": =DATEDIF(hire_date,as_of_date,"y") gives complete years. For years and months, add MOD(DATEDIF(hire_date,as_of_date,"m"),12) for the extra months. Point it at a fixed as-of date cell rather than TODAY() so reports do not change after you send them.

Q: How do I calculate turnover rate in Excel?

Divide the number of exits in the period by the average headcount for the period, where average headcount is (start headcount + end headcount) / 2. COUNTIFS with two date conditions counts the exits from a roster.

Q: What is a compa-ratio and how do I calculate it?

It compares pay with the midpoint of the pay band: salary / band midpoint. 1.00 is at midpoint, below 1.00 is below it. In Excel it is just =E2/F2, formatted with two decimals.

Q: How do I count only current employees?

Add a condition that the exit or termination date is blank: =COUNTIFS(Department,"Sales",ExitDate,""). COUNTBLANK(ExitDate) gives total current headcount.

For HR generalists, people ops teams and office managers who own the employee numbers. Your HRIS stores the records, but headcount by department, tenure, turnover for the board deck, PTO balances and pay-band checks often get built in Excel from an export. Every formula below runs on one small roster with an as-of date of October 31, 2026, and anything that comes from your policy, like an accrual rate or an overtime threshold, goes in its own input cell.

HR generalists and HR managers
People operations and HRIS analysts
Office managers who handle HR
Compensation analysts
Payroll coordinators

Spreadsheet help only, not legal or employment advice. Overtime, leave and pay rules vary by location; your policy and local law decide the numbers you enter.

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

	A	B	C	D	E	F	G	H
1	Employee	Department	Hire date	Exit date	Salary	Band midpoint	PTO accrued (hrs)	PTO used (hrs)
2	E-101	Operations	Apr 11, 2016		61000	60000	112	64
3	E-102	Operations	Sep 7, 2021	Jun 30, 2026	48500	52000	58	58
4	E-103	Sales	Jan 14, 2019		72000	70000	96	88
5	E-104	Sales	Mar 4, 2024		58000	70000	80	24
6	E-105	Finance	Aug 20, 2012		88000	82000	136	72
7	E-106	Operations	Nov 17, 2025		45500	52000	72	62
8	E-107	Sales	May 23, 2022	Feb 13, 2026	66000	70000	14	14
9	E-108	Finance	Jul 10, 2023		69500	72000	80	40
10	E-109	Operations	Feb 26, 2018		63500	60000	104	96
11	E-110	Customer Support	Nov 2, 2020		47000	48000	96	52
12	E-111	Customer Support	Jan 12, 2026		44000	48000	64	8

What Excel does for hr and people ops

Headcount by department, current staff only

A roster export usually includes people who have left, with an exit date filled in. COUNTIFS counts the rows for one department where the exit date is empty. The "" criterion means "is blank".

Put the department names in a column and point the formula at them (B15 instead of "Operations") to build the whole headcount table with one formula copied down. A PivotTable with Department in rows and a filter on Exit date (blank) does the same thing.

=COUNTIFS(B2:B12,"Operations",D2:D12,"")

Result: 3

Operations has 3 current employees (E-101, E-106, E-109). E-102 left in June, so they are not counted.

Tenure in years and months

DATEDIF with "y" gives complete years between two dates and "m" gives complete months. MOD(months,12) leaves the months past the last full year, so the two together read like 10 yrs 6 mos.

Use a fixed as-of date (here DATE(2026,10,31), or a cell holding it) instead of TODAY(), so a report you send keeps the same numbers. For people who have left, measure to the exit date: =DATEDIF(C3,IF(D3="",DATE(2026,10,31),D3),"y"). DATEDIF does not appear in Excel's function list or autocomplete, but it works when you type it.

=DATEDIF(C2,DATE(2026,10,31),"y")&" yrs "&MOD(DATEDIF(C2,DATE(2026,10,31),"m"),12)&" mos"

Result: 10 yrs 6 mos

E-101, hired April 11, 2016, has 10 yrs 6 mos of service on October 31, 2026.

Turnover rate for the year so far

A common way to calculate turnover is exits in the period divided by average headcount, where average headcount is (headcount at the start + headcount at the end) / 2. Here, everyone on the roster was hired before January 1 except E-111, and nobody left before then, so start headcount is 10. End headcount is everyone with no exit date, which COUNTBLANK gives you: 9.

Exits are counted with two date conditions on the Exit date column. Say which period and method you used next to the number, because turnover figures are only comparable when they are calculated the same way.

=COUNTIFS(D2:D12,">="&DATE(2026,1,1),D2:D12,"<="&DATE(2026,10,31))/((COUNTIFS(C2:C12,"<"&DATE(2026,1,1))+COUNTBLANK(D2:D12))/2)

Result: 0.21052631579 shows as 21.1% with Percent format

2 exits over an average headcount of 9.5: 21.1% turnover from January to October.

Find people who are low on PTO

The balance is accrued minus used. To count the current employees with fewer than 16 hours (two days) left, SUMPRODUCT can test both conditions row by row: exit date is blank, and accrued minus used is under 16. Each test gives TRUE or FALSE; multiplying them gives 1 only where both are true.

COUNTIFS cannot do this on its own because it cannot subtract one column from another inside the criteria. The other route is a Balance helper column, then COUNTIFS on that.

=SUMPRODUCT((D2:D12="")*(G2:G12-H2:H12<16))

Result: 3

3 current employees have under 16 hours left: E-103 (8), E-106 (10) and E-109 (8).

Compa-ratio for a department

Compa-ratio is salary divided by the midpoint of the pay band: 1.00 means paid exactly at midpoint, 0.90 means 10% below. For one person it is just =E2/F2. For a department average, SUMPRODUCT adds up each current Sales employee's ratio and COUNTIFS divides by how many there are.

Averaging the ratios is not the same as dividing total salary by total midpoints when midpoints differ. Pick one method and use it every time.

=ROUND(SUMPRODUCT((B2:B12="Sales")*(D2:D12=""),E2:E12/F2:F12)/COUNTIFS(B2:B12,"Sales",D2:D12,""),2)

Result: 0.93

Current Sales staff average a compa-ratio of 0.93: E-103 is at 1.03 and E-104 at 0.83, well below midpoint.

List next month's work anniversaries

MONTH pulls the month number out of each hire date. FILTER keeps the employee IDs where the month is 11 (November) and the person has not left, and TEXTJOIN joins them into one line you can paste into a reminder.

FILTER and TEXTJOIN need Excel 365 or Excel 2021 or later. In older versions, add a helper column with =MONTH(C2) and filter it to 11.

=TEXTJOIN(", ",TRUE,FILTER(A2:A12,(MONTH(C2:C12)=11)*(D2:D12="")))

Result: E-106, E-110

E-106 (1 year) and E-110 (6 years) have anniversaries in November.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

1How many people are currently employed (no exit date)?

Your formula for exercise 1

Show answer
=COUNTBLANK(D2:D12) = 9. COUNTBLANK counts empty cells, and an empty exit date means the person still works here: 9.
2What is E-101's PTO balance in hours?

Your formula for exercise 2

Show answer
=G2-H2 = 48. 112 hours accrued minus 64 used leaves 48 hours.
3What is E-104's compa-ratio?

Your formula for exercise 3

Show answer
=E5/F5 = 0.82857142857. $58,000 / $70,000 = 0.83, so E-104 is paid 17% below the midpoint of the band.
4How many current employees are in Sales?

Your formula for exercise 4

Show answer
=COUNTIFS(B2:B12,"Sales",D2:D12,"") = 2. Three people have been in Sales this year, but E-107 left in February, so the answer is 2.
5How many complete years of service does E-105 have on October 31, 2026?

Your formula for exercise 5

Show answer
=DATEDIF(C6,DATE(2026,10,31),"y") = 14. Hired August 20, 2012, so 14 complete years. DATEDIF only counts a year once the anniversary has passed.
6What is the total annual salary of current employees?

Your formula for exercise 6

Show answer
=SUMIFS(E2:E12,D2:D12,"") = 548500. SUMIFS adds the salaries only for rows with no exit date: $548,500.
7How many people left between January 1 and December 31, 2026?

Your formula for exercise 7

Show answer
=COUNTIFS(D2:D12,">="&DATE(2026,1,1),D2:D12,"<="&DATE(2026,12,31)) = 2. E-102 (June) and E-107 (February) match both conditions: 2. Empty exit dates never match a date comparison, so current staff are left out.
8How many current employees are paid below 90% of their band midpoint?

Your formula for exercise 8

Show answer
=SUMPRODUCT((D2:D12="")*(E2:E12/F2:F12<0.9)) = 2. E-104 (0.83) and E-106 (0.88) are under 0.90, so 2 people to look at in the next pay review.

Free templates for hr and people ops

Guided project: Weekly overtime check

Split each person's week into regular and overtime hours and see what overtime costs.

6 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

How do I calculate years of service in Excel?

Use DATEDIF with "y": =DATEDIF(hire_date,as_of_date,"y") gives complete years. For years and months, add MOD(DATEDIF(hire_date,as_of_date,"m"),12) for the extra months. Point it at a fixed as-of date cell rather than TODAY() so reports do not change after you send them.

How do I calculate turnover rate in Excel?

Divide the number of exits in the period by the average headcount for the period, where average headcount is (start headcount + end headcount) / 2. COUNTIFS with two date conditions counts the exits from a roster.

What is a compa-ratio and how do I calculate it?

It compares pay with the midpoint of the pay band: salary / band midpoint. 1.00 is at midpoint, below 1.00 is below it. In Excel it is just =E2/F2, formatted with two decimals.

How do I count only current employees?

Add a condition that the exit or termination date is blank: =COUNTIFS(Department,"Sales",ExitDate,""). COUNTBLANK(ExitDate) gives total current headcount.

Excel for other fields

Updated November 2, 2026