How to Calculate Employee Tenure and PTO Accrual in Excel & Google Sheets

Tracking employee service milestones, vesting schedules, and tiered Paid Time Off (PTO) vacation accruals is standard practice for HR and People Operations teams.

This guide demonstrates how to calculate precise tenure (years, months, days), exact decimal tenure with YEARFRAC, and how to automate tiered annual PTO accrual policies.

---

1. Calculating Exact Tenure: Years, Months & Days

To display an employee’s exact service duration in a readable string (e.g., “4 yrs, 7 mos, 12 days”), use DATEDIF combined with TODAY():

=DATEDIF(B2, TODAY(), "Y") & " yrs, " & DATEDIF(B2, TODAY(), "YM") & " mos, " & DATEDIF(B2, TODAY(), "MD") & " days"

Breakdown of DATEDIF Unit Parameters:

• "Y": Complete full years between the two dates.
• "YM": Remaining months after subtracting full years.
• "MD": Remaining days after subtracting full months.
• "M": Total full months between dates.
• "D": Total days elapsed between dates.

---

2. Decimal Tenure with YEARFRAC

If you need a numeric value for mathematical modeling (e.g. calculating severance pay or stock option vesting), use YEARFRAC:

=YEARFRAC(Hire_Date, TODAY(), 1)

• Argument 1: Tells Excel to use the actual/actual day-count convention for leap years.
• Example: Hire Date 01/15/2021 evaluated on 08/20/2026 returns 5.60 years.

---

3. Tiered PTO Vacation Accrual Model

Most US corporate PTO policies award vacation days based on employee tenure tiers. For example:

• Under 1 year: 10 PTO days/year (0.833 days/month)
• 1 to 3 years: 15 PTO days/year (1.250 days/month)
• 3 to 5 years: 20 PTO days/year (1.667 days/month)
• 5+ years: 25 PTO days/year (2.083 days/month)

Automated PTO Tier Formula (using IFS):

Assuming Hire Date is in B2:

=IFS(
  DATEDIF(B2, TODAY(), "Y") < 1, 10,
  DATEDIF(B2, TODAY(), "Y") < 3, 15,
  DATEDIF(B2, TODAY(), "Y") < 5, 20,
  TRUE, 25
)

---

Complete HR Roster Table Example

Row	Employee Name (A)	Hire Date (B)	Years of Service (C)	Readable Tenure (D)	Annual PTO Allowance (E)	Monthly Accrual Rate (F)
2	Emily Watson	03/12/2018	=YEARFRAC(B2, TODAY(), 1)	=DATEDIF(B2, TODAY(), "Y") & " yrs, " & DATEDIF(B2, TODAY(), "YM") & " mos"	=IFS(C2<1,10, C2<3,15, C2<5,20, TRUE,25)	=E2 / 12
3	Marcus Vance	11/01/2022	=YEARFRAC(B3, TODAY(), 1)	=DATEDIF(B3, TODAY(), "Y") & " yrs, " & DATEDIF(B3, TODAY(), "YM") & " mos"	=IFS(C3<1,10, C3<3,15, C3<5,20, TRUE,25)	=E3 / 12
4	Chloe Bennett	09/15/2025	=YEARFRAC(B4, TODAY(), 1)	=DATEDIF(B4, TODAY(), "Y") & " yrs, " & DATEDIF(B4, TODAY(), "YM") & " mos"	=IFS(C4<1,10, C4<3,15, C4<5,20, TRUE,25)	=E4 / 12

Calculated Output (as of August 2026):

• Emily Watson (Hired March 2018): Tenure: 8.44 yrs (8 yrs, 5 mos) $\rightarrow$ Annual PTO: 25 days (2.08 days/mo).
• Marcus Vance (Hired Nov 2022): Tenure: 3.80 yrs (3 yrs, 9 mos) $\rightarrow$ Annual PTO: 20 days (1.67 days/mo).
• Chloe Bennett (Hired Sept 2025): Tenure: 0.93 yrs (0 yrs, 11 mos) $\rightarrow$ Annual PTO: 10 days (0.83 days/mo).

---

4. Calculating Accrued PTO To-Date (Current Year)

To calculate how much PTO an employee has accrued from January 1 of the current year through today’s date:

= (E2 / 12) * (MONTH(TODAY()) + DAY(TODAY()) / 30)

Or for semi-monthly payroll cycles (24 pay periods per year):

= (E2 / 24) * Total_Pay_Periods_Completed

---

Pro Tip: Flagging Upcoming Work Anniversaries (Next 30 Days)

To highlight employees who have an upcoming service anniversary within the next 30 days for HR recognition:

=IF(
  AND(
    DATE(YEAR(TODAY()), MONTH(B2), DAY(B2)) >= TODAY(),
    DATE(YEAR(TODAY()), MONTH(B2), DAY(B2)) <= TODAY() + 30
  ),
  "🎉 Milestone Approaching",
  "Normal"
)