If statement help??

[mcintoshmc]

I created the below formula to calculate length of our hiring process. It will show pending, if the hire hasn't completed yet.

IF(R2="HIRED",IFERROR(DATEDIF(AF2,AV2,"D"),""),"PENDING")

Now, I want to account for hiring delays that are created by the candidate. Meaning, they're not looking to start for another 3 months, so I don't want this delay to skew our numbers. I created a delay column in AH. For the purposes of the above formula, AH2 will contain the letter "D".

I want the formula to return "DELAY" if AH2="D", "PENDING" if R2 doesn't ="HIRED", and the amount of days the hire took if both of aforementioned conditions are not met. I'm thinking this is a IF OR statement, but couldn't get it to work.

 

[philwojo]

Try this maybe:

=IF(or(R2<>"HIRED",AH2="D"),IFERROR(DATEDIF(AF2,AV2,"D"),""),"PENDING")

 

[AlphaFrog]

Wrap it in another IF function...

IF(AH2="D","DELAY",IF(R2="HIRED",IFERROR(DATEDIF(AF2,AV2,"D"),""),"PENDING"))

 

[mcintoshmc]

Try this maybe:

=IF(or(R2<>"HIRED",AH2="D"),IFERROR(DATEDIF(AF2,AV2,"D"),""),"PENDING")

This one didn't quite return the result I was looking for. Thanks.

 

[mcintoshmc]

Wrap it in another IF function...

IF(AH2="D","DELAY",IF(R2="HIRED",IFERROR(DATEDIF(AF2,AV2,"D"),""),"PENDING"))

This worked great! Now, how can I alter it, so that it will not show PENDING all the way down the spreadsheet because this will affect my pivot table.

 

[AlphaFrog]

This worked great! Now, how can I alter it, so that it will not show PENDING all the way down the spreadsheet because this will affect my pivot table.

You're welcome.

I worked great but you don't want to show "Pending"? I don't know what that means.

If you want to ignore empty cells, test a key cell if it's empty

=IF(AV2 = "", "",IF(AH2="D","DELAY",IF(R2="HIRED",IFERROR(DATEDIF(AF2,AV2,"D"),""),"PENDING")))