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

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    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

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭

    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

  • NikkiOno
    NikkiOno ✭✭✭✭

    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?

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭

    @NikkiOno

    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

  • NikkiOno
    NikkiOno ✭✭✭✭

    @Tomasz Giba This worked! And thank you so much for breaking it down and explaining it.

  • NikkiOno
    NikkiOno ✭✭✭✭

    @Tomasz Giba

    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.


  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    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

  • NikkiOno
    NikkiOno ✭✭✭✭

    @Tomasz Giba

    That worked! Thanks so much for the quick response.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!