Vacation Tracker - formula between dates

Camie Keuck

Hi All

I have a formula question - I am looking for the number of vacation days to populate when an anniversary date is between a certain date range. For instance with an anniversary date of 03/05/1997, if dates are between 03/05/1997 and 03/04/2002 then I want the value to be 10. (10 days of PTO for first 5 years) For dates between 03/05/2002 and 03/04/2009 then I want the value to be 15. For a date greater than or equal to 03/05/2009 I want the value to equal 20.

Any help with where to start with something like this would be much appreciated. Screenshot attached for reference - thank you!

Chris Rea

is this kind of what you're looking for:

the formula that I used for the "Total PTO Earned" column is this: =IF([years employed]@row <= 5, 10, IF(AND([years employed]@row > 5, [years employed]@row <= 10), 15, 20))

and the formula for the "years employed" column is this: =YEAR(TODAY()) - YEAR([hire date]@row)

Basically I'm giving an employee 10days of PTO for the first 5 years of employment. Then every 5 extra days for years 6-10, and finally 20 days if they've worked over 10 years.

Chris Rea

is this kind of what you're looking for:

the formula that I used for the "Total PTO Earned" column is this: =IF([years employed]@row <= 5, 10, IF(AND([years employed]@row > 5, [years employed]@row <= 10), 15, 20))

and the formula for the "years employed" column is this: =YEAR(TODAY()) - YEAR([hire date]@row)

Basically I'm giving an employee 10days of PTO for the first 5 years of employment. Then every 5 extra days for years 6-10, and finally 20 days if they've worked over 10 years.

Camie Keuck

Thank you thank you thank you!! Those equations worked perfectly after I added the Years Employed column. 😁