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!

 

[DigitalData]

[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!

C2 = hired date is what you meant I believe? Other than that I see where you are going with this formula haha. I am trying validate it as we speak!

 

[DigitalData]

What I do not understand is B2=""... why would b2 every be blank? It will always have a date there.

 

[DigitalData]

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

I see where you are going with the formula, but what would the overall formula be? That would be basically just for one of them correct? I am assuming the H Q R columns are from the table?

 

[Beyond_avarice]

$Q$7:$R$10 is a table I set up for those over 90 days. Adjust to wherever you place this table.

$Q$3:$R$5 is a table I set up for those with 90 days or less. Adjust to wherever you place this table.

I might have errored with "H" as I believe your intention was to have "G"= the number of absences.

You should be able to autofill down your table of employees and it will work for those with 90 days or less and those with over 90 days.

 

[DigitalData]

$Q$7:$R$10 is a table I set up for those over 90 days. Adjust to wherever you place this table.

$Q$3:$R$5 is a table I set up for those with 90 days or less. Adjust to wherever you place this table.

I might have errored with "H" as I believe your intention was to have "G"= the number of absences.

You should be able to autofill down your table of employees and it will work for those with 90 days or less and those with over 90 days.

For whatever reason yours are spitting out blanks, but the ones that do fill are correct though! Here is an example below of what blanked out. Disregard second column the first column was $G and is the absences. I believe it is missing a greater than 3 clause for the less than 90 days and probably the same for greater than 90 days?

[TABLE="width: 531"]
<tbody>[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]67[/TD]
[TD]First Warning Verbal[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]67[/TD]
[TD]Final Warning Written[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]66[/TD]
[TD]Termination[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]10[/TD]
[TD]62[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Beyond_avarice]

Change the second Vlookup to "TRUE".

 

[DigitalData]

Change the second Vlookup to "TRUE".

Works perfectly! I really appreciate the help!

 

[DigitalData]

Change the second Vlookup to "TRUE".

Hey Avarice,

I had a question. This did work, but I have one more wrench to throw in it. What if after 90 days of employment you wanted to reset the persons number of absences / tardies to zero. I am sure you would need to refresh on the data above. I believe it is all there, but if you need another example file I can make one up.

Basically after 90 days employment they get a "clean slate". Therefor resetting the tardies and absences to zero. However, I have a tab with that data pulled from a system. Is there a way to do this via formula? So it is working and saying here are the set of disciplines before or after 90 days. I just need a way to reset them to zero, because someone has hit 90 days.

Current formula for absence discipline: =IFERROR(IF($M2>90,VLOOKUP($J2,'List Table'!$A$5:$B$8,2,TRUE),VLOOKUP($J2,'List Table'!$A$1:$B$3,2,TRUE)),"No Action")

Data set I do have: I have the Persons name, what type of discipline and what date each single occurrence happened on.

What I was thinking Idea #1: If you have the hire date of person A, the current date and all individual days that each tardy or absence happened you could have a formula saying disregard the first 90 days and those disciplines and only add those.
What I was thinking Idea #2: have a column that says on what day the 90th day was. Then calculate from that date. For example similar to the Vlookup above it will first look for the 90 day and absences on the other tab after that date. If NA then it would look from the hire date.
What I was thinking Idea #3 : A combination of 1 or 2 and then some manual intervention. Or having two separate tabs entirely. Pre 90 day employees and Post 90 day Employees.

Let me know any questions you may have I can clear them up or send and example file! Appreciate the help ahead of time!