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
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!