Need PTO available IF full-time & ....

NikkiOno

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

Tomasz Giba

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

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!

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

@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.

NikkiOno

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

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

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!

NikkiOno

@Tomasz Giba

That worked! Thanks so much for the quick response.