Nested IF statements combined with Tenure Length

[DigitalData]

All,

I am a Data Reporting Analyst and I could use a little help. I am trying to return results for the following. What I am trying to find is an employee that has been with the company greater than or less than 90 days IF so how many absences in one column return the value based on the absent policy.

Formula so far: =IF($G2=1,"No Action",IF($G2=2," .. etc etc that part is easy I just need to figure out how to look for if that person's tenure length is greater or less than 90 THEN look for the nested IFs afterwards!

-Has this person been here greater than or less than 90 days? ($i2)
-Based on how many unexcused absences ($G2)
-Then return Written warning etc as seen below

[TABLE="width: 357"]
<tbody>[TR]
[TD="colspan: 2"]First 90 Days Employment
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]First Warning Verbal
[/TD]
[TD]1 absences
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Final Warning Written
[/TD]
[TD]2 absences
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Termination
[/TD]
[TD]3 absences
[/TD]
[/TR]
[TR]
[TD="colspan: 3"]
After 90 Days of Employment (Rolling 12 Months)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]First Warning Verbal
[/TD]
[TD]2 absences
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Final Warning Written
[/TD]
[TD]4 absences
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Termination
[/TD]
[TD]5 absences
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Termination
[/TD]
[TD]6 absences
[/TD]
[/TR]
</tbody>[/TABLE]


THANK YOU in advance and let me know if I need to clear anything up!

 

[SpillerBD]

You probably should consider CHOOSE, VLOOKUP or INDEX functions for your evaluation.

 

[seguin85]

Can you provide an example of how the data is setup? One way of figuring out how long their tenure is would be: =today()-A2, where A2 is the start date.

 

[DigitalData]

You probably should consider CHOOSE, VLOOKUP or INDEX functions for your evaluation.

I do not need a VLookup it is already there. What I need is way to add in the formula for greater than or less than 90 days.

 

[Beyond_avarice]

So you indicate that you want $I2 (I presume this is relative as you will autofill down =$I3, $I4 etc), as an indictor of tenure.

=IF(TODAY()-A1>90,TRUE, FALSE)

A1 needs to be a Hire date/Seniority Date.

 

[DigitalData]

Can you provide an example of how the data is setup? One way of figuring out how long their tenure is would be: =today()-A2, where A2 is the start date.

Dropbox - Example.xlsx

 

[DigitalData]

Here is the file. ALL I need is in column J is:
Dropbox - Example.xlsx

1) Has this person been here greater than 90 days or less
2) ALSO IF they have based on how many absences they have in column G
3) Based on column G what is the result of discipline.. which is in my original post..

It should be something along these lines, but the AND formula will now work here

=IF(AND($i2>90,$G2=1,$G2=2,"$G2=3,$G2=4,$G2=5=>),FIRST VERBAL WARNING, FINAL WRITTEN WARNING,TERMINATION, TERMINATION)

 

[Beyond_avarice]

$G2 cannot equal 1,2,3,4,5 at the same time. Use and "OR" statement.

=IF([U]AND[/U]($i2>90,[COLOR=#ff0000]OR[/COLOR]($G2=1,$G2=2,"$G2=3,$G2=4,$G2=5=>)),FIRST VERBAL WARNING, FINAL WRITTEN WARNING,TERMINATION, TERMINATION)

Should have read further before commenting. So if the tenure is over 90 days and there is a G2 of 1; then you want:"First Verbal warning, G2 of 2:"Final Written Warning", etc.

 

[estevaoba]

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Try this:

Considering that:
B2=hired date
I2=tenure
G2=number of unexcused absences

=IF(B2="","",IF(AND(I2<=90,G2<=1),"First Warning Verbal",IF(AND(I2<=90,G2<=2),"Final Warning Written",IF(AND(I2<=90,G2>2),"Termination",IF(AND(I2>90,G2<=2),"First Warning Verbal",IF(AND(I2>90,G2>2,G2<=3),"Final Warning Written","Termination"))))))[/TD]
[/TR]
</tbody>[/TABLE]

Godspeed!

 

[Beyond_avarice]

Set up two tables like so:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]First Warning-Verbal
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Final Warning- Written
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Termination
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]2
[/TD]
[TD]First Warning-Verbal
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Final Warning- Written
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Termination
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Termination
[/TD]
[/TR]
</tbody>[/TABLE]

=IFERROR(IF(I3>90,VLOOKUP(H3,$Q$7:$R$10,2,TRUE),VLOOKUP(H3,$Q$3:$R$5,2,FALSE)),"")