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 6-10, 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 6-10, 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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!