Vacation Tracker  formula between dates
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!
Best Answer

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 610, and finally 20 days if they've worked over 10 years.
Answers

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 610, and finally 20 days if they've worked over 10 years.

Thank you thank you thank you!! Those equations worked perfectly after I added the Years Employed column. 😁
Help Article Resources
Categories
Check out the Formula Handbook template!