How can I do an IF Statement with Years
Hello,
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

There's likely a more elegant way of doing this but here's what I did.
Created an additional column called Years of Emp. And used this formula:
=(TODAY()  [Hire Date]@row) / 365
Then in your Vacation Days Available, I used this:
=IF([Years of Emp]@row > 6, 10, IF([Years of Emp]@row > 5, 9, IF([Years of Emp]@row > 4, 8, IF([Years of Emp]@row > 3, 7, IF([Years of Emp]@row > 2, 6, IF([Years of Emp]@row > 1, 5, 0))))))  ([Vacation Days Used]@row)

Try something like this.
=IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) = 1, 5, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 2) = 2, 6, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) = 3, 7, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) = 4, 8, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) = 5, 9, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) >= 10, 6))))))
Answers

There's likely a more elegant way of doing this but here's what I did.
Created an additional column called Years of Emp. And used this formula:
=(TODAY()  [Hire Date]@row) / 365
Then in your Vacation Days Available, I used this:
=IF([Years of Emp]@row > 6, 10, IF([Years of Emp]@row > 5, 9, IF([Years of Emp]@row > 4, 8, IF([Years of Emp]@row > 3, 7, IF([Years of Emp]@row > 2, 6, IF([Years of Emp]@row > 1, 5, 0))))))  ([Vacation Days Used]@row)

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

Try something like this.
=IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) = 1, 5, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 2) = 2, 6, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) = 3, 7, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) = 4, 8, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) = 5, 9, IF(ROUNDDOWN((TODAY()  [Hire Date]@row) / 365, 1) >= 10, 6))))))

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

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
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!