PTO Accrual Tracker

[mr shifty]

I am trying to calculate PTO for my company of 600+ employees. I have used a few of the formulas posted here over the years, but can't seem to get it work per our policy

Example:
If an employee has a hire date of 6/3/18, from 12/16/18 through today would equal 6 pay periods at the <1 year rate equaling 22.158 hours (6*3.693). The issue is when the employee would get to their 1 year anniversary which would be the 12th pay period (6/2/19). At 12 pay periods, the employee will have 44.316 (12*3.693) hours and then on the 13th pay period start accruing at the 4.309 rate. Is there a formula to show this? Most formulas I have used change the total balance from 44.316 to 51.696 (12*4.308).

Accrued biweekly starting 12/16/18:
0-1 years = 3.693 PTO hours/biweekly
1-5 years = 4.308 PTO hours/biweekly
5+ years = 5.539 PTO hours/biweekly

So far I have the following:
C1 start of the pay period 12/16/2018
D1 Today
E1 =INT((D1-C1)/14)
A3 Name
B3 Hire Date
C3 show the accrued balance

Thanks!

 

[mr shifty]

To clarify, I am getting a value of zero

 

[mr shifty]

Nvm, I'm a little slow and didn't realize there was a NOW function. All good!!!

 

[Jeffrey Mahoney]

Ok, Good. I'm glad things are working.