How can I do an IF Statement with Years


I have been trying to find an answer to my problem through the forums but have been unable to solve it myself currently.

What I am trying to do is set up a formula that tells us when employees get vacation days. How our company currently works is as follows:

If employee has been here 1 year, they get 5 days vacation

If they have been here 2 years, 6 days vacation.

3 years = 7 days vacation.

4 years = 8 days vacation, and so on until they max out at 10 days vacation days.

I also need to be able to deduct the amount of vacation days they have already used (reflected against another column where we already have the formula working). For the sake of ease will call this column "Vacation Days Used" and the formula I need help making would be under "Vacation days available"

So, How do I set up an If statement that reads: If hire date is greater than 1 year = 5, if hire date is greater than 2 years = 6, if hire date is greater than 3 years = 7 days, if hire date is greater than 4 years = 8 days, if hire date is greater than 5 years = 9 days, if hire date is greater than 6 years = 10 days + - "Vacation Days Used" Column

Thank you! I appreciate any help I can get in making this formula.

Best Answers


  • @Nic Larsen You are a mad genius !! That worked perfectly. Thank you thank you!!!

  • @John Jonassen Thank you! I'll take a look at that. I'm saving all of these formulas like a hoarder.

  • John Jonassen
    John Jonassen ✭✭✭✭

    HA!! It's doing the same that @Nic Larsen provided, within a single column. It's dividing the difference between today's date and the Hire Date by the 365 days in the year. That will then Round down to the nearest full completed year of service. Then based that, you get if it equals 1, 5 Days, 2, 6 Days, and so on. I think I got lost at the end and mixed up the 6 years and 10 days. But at least the idea is planted.

  • Haha I think so too! But I really appreciate all sorts of different examples. It helps my brain function the formulas out better in the future. Haha but yes I definitely get the idea. So excited!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!