Count active staff in one department
How many employees in Ops have the status Active?
Formula: =COUNTIFS(B2:B6,"Ops",D2:D6,"Active") → 2
Hands-on Excel exercises
Excel Skills for HR
Practice Excel for HR: tenure with DATEDIF, headcount by department and status with COUNTIFS, and worked answers on a fictional staff table.
HR spreadsheets are about people, dates, and categories: who joined when, how long they have been here, and how many are in each team or status. Interviewers for HR and people-operations roles often check three skills together: calculating tenure from a start date, counting by department and status, and keeping the data tidy without exposing more than needed.
The live sheet is a five-person table with department, start date, and status, plus a cutoff date in E2. Your task is to calculate the full years of service for the first employee as of that cutoff with DATEDIF. The worked answers count active employees in Operations with COUNTIFS, count people on leave, and compute another person's tenure in years and a third person's in months, which shows how the unit argument changes the answer.
Use fictional data for practice, and in real work, aggregate where a summary is enough rather than sharing individual records. Treat date columns carefully: real dates are numbers, and text that only looks like a date will not calculate. If a tenure comes out as an error, check that the start date is not after the cutoff. For reports that grow into monthly summaries, the administrative assistants and project managers pages cover lists and schedules.
Your turn
Your turn: in F2, calculate the complete years of service of the employee in row 2, from the start date in C2 to the cutoff date in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Department | Start date | Status | Cutoff | |
| 2 | Ana | Sales | 3/15/2019 | Active | 6/30/2024 | |
| 3 | Ben | Ops | 11/1/2021 | Active | ||
| 4 | Cy | Sales | 8/21/2023 | Active | ||
| 5 | Dee | Finance | 1/10/2016 | Leave | ||
| 6 | Eli | Ops | 6/30/2022 | Active |
Not yet solved.
Answer: =DATEDIF(C2,E2,"Y")
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In E3, count how many employees are in the list.
- In G2, build the date December 31, 2026 with the DATE function.
- In E2:E4, count how many deals each manager has.
- In E2:E5, keep only the digits of the phone number: remove the spaces, parentheses and hyphens.
- Build a PivotTable: the sum of sales by rep, with the result in F1.
- In G2:G6, calculate the withholding (the rate is in N2), rounded to the dollar.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Count people on leave
How many employees have the status Leave?
Formula: =COUNTIF(D2:D6,"Leave") → 1
Tenure in years
How many complete years of service does Dee (row 5) have at the cutoff date?
Formula: =DATEDIF(C5,E2,"Y") → 8
Tenure in months
How many complete months of service does Cy (row 4) have at the cutoff date?
Formula: =DATEDIF(C4,E2,"M") → 10
Frequently asked questions
Why does DATEDIF take a text unit?
The last argument chooses Y for years, M for months, or D for days between the two dates.
Why does my date column not calculate?
It may be stored as text. A real date is a number formatted as a date; convert text with DATEVALUE or re-enter it.
Can I use real employee data to practice?
Use fictional data. Real records are personal data and do not belong in practice sheets.
How do I count by department and status?
Use COUNTIFS with a range and criteria for each condition, as in the first worked answer.