Calculate PTO Time by Start Date

[concretetsunami]

PTO Calculation.xlsI am making a spreadsheet that calculates PTO for employees. Employees with 0-5 yrs get 2.308 hours a week up to 120 hrs/year. Employees with 5+ yrs get 3.077 hours a week up to 160 hrs/year. I am attaching the setup that I would like to use. I came up with a formula that works except for 1 problem:
If the employee reached their 5 yr mark during the year,such as employee #2 does in the attachment, how can I formulate 2.308hrs/week up until they reach their 5yr mark and then do 3.077hrs/week from then on out?

[rwgrietveld]

Some of these posts .... Hope you like it.

This will give you the PTO as per today.
Input is start date.
This can be anything < today (also yesterday)

=CHOOSE(IF(DATE(YEAR(B6)+5,MONTH(B6),DAY(B6))<DATE(2010,1,1),0,IF(DATE(YEAR(B6)+5,MONTH(B6),DAY(B6))<TODAY(),1,2))+1,MIN((TODAY()-DATE(2010,1,1))*3.077,160),MIN((DATE(YEAR(B6)+5,MONTH(B6),DAY(B6))-DATE(2010,1,1))*2.308+(TODAY()-DATE(YEAR(B6)+5,MONTH(B6),DAY(B6)))*3.077,160),MIN((TODAY()-MAX(DATE(2010,1,1),B6))*2.308),120)

This is my test

It works, but I hope someone will find a lookup table approach.