Date difference in months
IntermediateHR and payroll reports often need tenure in months so you can group employees by time-in-role, apply benefit rules, or audit probation periods.
In this sheet, each employee has a hire date, and there’s a single “As of date” at the top (cell B1). Your job is to calculate tenure as the number of complete months between those two dates.
What you need to do:
- In cell D3, calculate the employee’s tenure in months using the hire date in C3 and the as-of date in B1.
- Fill the formula down through D10 for the remaining employees.
Tip: The as-of date should stay fixed when you fill down, but the hire date should change row-by-row.
Need some help?
Hint 1
DATEDIF can return complete months when you use the unit "m".
Hint 2
The end date is the as-of date in B1. Lock it (with $) so it doesn’t shift when you fill down.
Hint 3
When you fill down, only the hire-date reference should move from C3 to C4, C5, etc.
Answer
Exercise
Date difference in months
IntermediateHR and payroll reports often need tenure in months so you can group employees by time-in-role, apply benefit rules, or audit probation periods.
In this sheet, each employee has a hire date, and there’s a single “As of date” at the top (cell B1). Your job is to calculate tenure as the number of complete months between those two dates.
What you need to do:
- In cell D3, calculate the employee’s tenure in months using the hire date in C3 and the as-of date in B1.
- Fill the formula down through D10 for the remaining employees.
Tip: The as-of date should stay fixed when you fill down, but the hire date should change row-by-row.
Need some help?
Hint 1
DATEDIF can return complete months when you use the unit "m".
Hint 2
The end date is the as-of date in B1. Lock it (with $) so it doesn’t shift when you fill down.
Hint 3
When you fill down, only the hire-date reference should move from C3 to C4, C5, etc.