Need PTO available IF full-time & ....
I am having trouble creating a formula to show pto available for each employee. There are two IFs and each IF has an AND. below is what i came up with and its not working although the IF(AND works when i only use one IF. Hope that makes sense.
Ex: if an employee has worked for less than 1 year and they are full time, they get 10 days divided by the months they have been employed. if an employee has worked for more than 9 years and full time, they get 10 days and for anything greater than 9 years, they get 15 days.
The -[PTO USED]@row is subtracting from the amount of pto they have already used.
=IF(AND(YEAR(TODAY()) = (YEAR([DATE OF HIRE]@row)), [EMPLOYMENT TYPE]@row ="FULL-TIME"), ROUNDUP((12 - MONTH([DATE OF HIRE]@row)) / 12 * 10) - [PTO USED]@row), IF(AND(YEAR(TODAY()) - YEAR([DATE OF HIRE]@row) < 9, [EMPLOYMENT TYPE]@row = "FULL-TIME"), 10, 15)-[PTO USED]@row
Best Answer
-
Hi @NikkiOno
Let me know if this will work:
=IFERROR(IF([employment type]@row = "full-time", IF(TODAY() - [start date]@row > 3287, 15, IF(TODAY() - [start date]@row > 365, 10, ROUNDDOWN((TODAY() - [start date]@row) / 30.416 * 10 / 12))) - [pto used]@row, 0), "")
Thanks for the feedback!
Tomasz Giba
Answers
-
Hi @NikkiOno
Check this:
=IF([EMPLOYMENT TYPE]@row = "FULL-TIME", IF((TODAY() - [DATE OF HIRE]@row) <= 365, ROUNDUP((MONTH(TODAY()) - MONTH([DATE OF HIRE]@row)) * 10 / 12), IF(AND(YEAR(TODAY()) - YEAR([DATE OF HIRE]@row) >= 1, YEAR(TODAY()) - YEAR([DATE OF HIRE]@row) <= 9), 10, IF(AND(YEAR(TODAY()) - YEAR([DATE OF HIRE]@row) > 9, YEAR(TODAY()) - YEAR([DATE OF HIRE]@row) < 15), 15)))) - IF([EMPLOYMENT TYPE]@row = "FULL-TIME", [PTO USED]@row)
Please let me know if it works for you.
Good Luck!
Tomasz Giba
-
Hi @Tomasz Giba, thank you for your reply. The formula works for some of the employees but not for others.
Laura has been employed since 09/25/84 and is full-time so she should have 15 days total but has used 14 and should have one left. when i put the formula you sent me in, it gives me "-14".
Also, please explain why we don't need the IF(AND) in the first logical expression?
-
OK, second attempt:
=IF([EMPLOYMENT TYPE]@row = "FULL-TIME", IF(ROUNDDOWN(NETDAYS([DATE OF HIRE]@row, TODAY()) / 365.25) > 9, 15, IF(AND(ROUNDDOWN(NETDAYS([DATE OF HIRE]@row, TODAY()) / 365.25) >= 1, ROUNDDOWN(NETDAYS([DATE OF HIRE]@row, TODAY()) / 365.25) <= 9), 10, IF((TODAY() - [DATE OF HIRE]@row) <= 365, ROUNDUP((MONTH(TODAY()) - MONTH([DATE OF HIRE]@row)) * 10 / 12))))) - IF([EMPLOYMENT TYPE]@row = "FULL-TIME", [PTO USED]@row)
Division by 365.25 is to compensate an extra day every four years.
IF(AND) explanation:
Checking the "FULL-TIME" is there, just differently nested.
Your original nesting is:
AND(this1=OK,this2=OK)
Nesting used here looks like this:
IF(this1=OK,IF(this2=OK))
The reason why I used a second scenario is that checking "FULL-TIME" is done only once on the begging so I don't have to repeat it later.
Let me know if this works or need another revision / adjustment.
Tomasz Giba
-
@Tomasz Giba This worked! And thank you so much for breaking it down and explaining it.
-
Hi tomasz, I am having a problem with the formula you gave me for employees who have worked less than one year but more than 8 months. the formula works with every other date but this one. its giving me a negative number and i don't understand why.
=IF([employment type]@row = "FULL-TIME", IF(ROUNDDOWN(NETDAYS([start date]@row, TODAY()) / 365.25) > 9, 15, IF(AND(ROUNDDOWN(NETDAYS([start date]@row, TODAY()) / 365.25) >= 1, ROUNDDOWN(NETDAYS([start date]@row, TODAY()) / 365.25) <= 9), 10, IF((TODAY() - [start date]@row) <= 365, ROUNDUP((MONTH(TODAY()) - MONTH([start date]@row)) * 10 / 12))))) - IF([employment type]@row = "FULL-TIME", [pto used]@row). Please let me know if you can help me figure this out.
-
Hi @NikkiOno
Let me know if this will work:
=IFERROR(IF([employment type]@row = "full-time", IF(TODAY() - [start date]@row > 3287, 15, IF(TODAY() - [start date]@row > 365, 10, ROUNDDOWN((TODAY() - [start date]@row) / 30.416 * 10 / 12))) - [pto used]@row, 0), "")
Thanks for the feedback!
Tomasz Giba
-
That worked! Thanks so much for the quick response.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!