Challenge Scenario
Human resources departments celebrate work anniversaries by recognizing employee loyalty. Using the audit reference date in $G$1 (2026-09-01), companies grant tiered anniversary milestone awards:
- 5 or more years (
C2 >= 5): "5-YR MILESTONE" (Executive Pen & Bonus).
- 3 or 4 years (
C2 >= 3): "3-YR MILESTONE" (Commemorative Plaque).
- 1 or 2 years (
C2 >= 1): "1-YR MILESTONE" (Company Pin).
- Less than 1 year:
"NEW HIRE".
As an HR People Operations Manager, you are preparing the anniversary roster:
- Tenure in Years (Column C): Calculate completed full years of service using
=INT(($G$1-B2)/365).
- Milestone Award (Column D): Assign the award tier using nested IF:
=IF(C2>=5, "5-YR MILESTONE", IF(C2>=3, "3-YR MILESTONE", IF(C2>=1, "1-YR MILESTONE", "NEW HIRE"))).
- Average Company Tenure (Cell C8): Calculate average tenure using
AVERAGE(C2:C6).
- Total 5-Year Veterans (Cell D8): Count employees eligible for the 5-year award using
COUNTIF(D2:D6, "5-YR MILESTONE").
Your Sheet Layout
Understanding the employee tenure schedule:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B6 |
Employee, Hire Date |
Employee Master |
Staff names and official start dates |
Read-only employee directory. |
G1 |
Review Date |
Anchor Date |
Audit reference date: 2026-09-01 |
Anchor date for tenure calculation. |
C2:C6 |
Tenure (Yrs) |
Years of Service |
=INT(($G$1-B2)/365) |
Calculates completed years with company. |
D2:D6 |
Milestone Award |
Reward Tier |
=IF(C2>=5, "5-YR MILESTONE", IF(C2>=3, "3-YR MILESTONE", IF(C2>=1, "1-YR MILESTONE", "NEW HIRE"))) |
Assigns anniversary gift eligibility. |
C8 |
Average Tenure |
Summary |
=AVERAGE(C2:C6) |
Average team service duration (3.2 years). |
D8 |
5-Year Veterans |
Summary |
=COUNTIF(D2:D6, "5-YR MILESTONE") |
Total senior veterans eligible for awards (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells C2:C6, calculate completed years of service (= INT(($G$1 - Hire Date)/365)).
2
In cells D2:D6, assign milestone award ("5-YR MILESTONE", "3-YR MILESTONE", "1-YR MILESTONE", "NEW HIRE").
3
In cell C8, calculate average tenure years with AVERAGE.
4
In cell D8, count employees who achieved "5-YR MILESTONE" status.
Solution & Formula Breakdown
Let's review the tenure and milestone award calculations:
1. Service Tenure with INT (C2:C6)
=INT(($G$1-B2)/365)
- Anthony (hired 2020-03-01): 2,375 days / 365 = 6 years.
- Natasha (hired 2023-05-15): 1,205 days / 365 = 3 years.
- Peter (hired 2025-08-10): 387 days / 365 = 1 year.
- Bruce (hired 2026-02-01): 212 days / 365 = 0 years.
- Steve (hired 2019-11-20): 2,477 days / 365 = 6 years.
2. Milestone Award Classification (D2:D6)
=IF(C2>=5, "5-YR MILESTONE", IF(C2>=3, "3-YR MILESTONE", IF(C2>=1, "1-YR MILESTONE", "NEW HIRE")))
- Anthony (6 yrs): >= 5 → 5-YR MILESTONE.
- Natasha (3 yrs): >= 3 → 3-YR MILESTONE.
- Peter (1 yr): >= 1 → 1-YR MILESTONE.
- Bruce (0 yrs): < 1 → NEW HIRE.
- Steve (6 yrs): >= 5 → 5-YR MILESTONE.
3. Summary Metrics (C8 & D8)
- Cell C8 (Average Tenure):
=AVERAGE(C2:C6) → 3.2 years.
- Cell D8 (5-Year Veterans Count):
=COUNTIF(D2:D6, "5-YR MILESTONE") → 2 veterans.
Using INT(...) truncates decimals, giving clean completed whole years rather than messy fractions like 3.301 years!