How can I do an IF Statement with Years

Julie Kelsch

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.

Nic Larsen

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)

John Jonassen

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))))))

Nic Larsen

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)

Julie Kelsch

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

John Jonassen

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))))))

Julie Kelsch

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

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.

Julie Kelsch

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!