Help with date formula's

I am creating a tracker that will keep track of vacation days, and the number of days an employee gets each year depends on how long they have been an employee. The days are earned each year on their anniversary.

I have almost everything working with the exception of 1 thing.

Let's say I have an employee that started 10/12/2016, as of that date he will get 13 vacation days but not before that date.

Right now I have this as a formula in the cell for Vacation Days Alotted:

=IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 1, 3, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 2, 5, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 4, 10, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 6, 13, IF(YEAR(TODAY()) - YEAR([Date of Hire]6) < 9, 15, 18)))))

My result is 13 days. Right now since it is not yet 10/12/20 it should only show 10 days. Does anyone have suggestions on what I need to change in my formula to correct this?

Thanks in advance for any help!




Best Answer


  • Hello @ToriLynn,

    Thank you so much, this is exactly what I was looking for. It works much better all the way around. I really

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!