• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vacation Days Based of Hire Date and Schedule


New Member
Help! I am new to all of this and need a formula to calculate the number of vacation days granted based on the employee's hire date and the vacation day allotment schedule in the handbook.

After 1 year, 10 days vacation
After 5 years, 15 days vacation
After 10 years, 17 days vacation
After 15 years, 20 days vacation
After 20 years, 25 days vacation

Any help or guidance you could give me would be appreciated! This project kinda landed in my lap and I'm new to using Excel.


S. Das

Active Member
Please try the below code in H7 and copied down.
=IF(DATEDIF(E7,F7,"y")>=20,25,IF(DATEDIF(E7,F7,"y")>=15,20,IF(DATEDIF(E7,F7,"y")>=10,17,IF(DATEDIF(E7,F7,"y")>=5,15,IF(DATEDIF(E7,F7,"y")>=1,10)))))&" Days"

Note:: for <1 year no logic is there, so for this the result will be "False Days".