Hi,
I have been trying to achieve below result in excel but unable to do so as i am getting 'False' error. Can anybody please help me?
If WFA/On Hold, Force Date + Delay
If R3 Date <= End of Month:
If Offer Process
If Force Date <= End of Month, Force Date + Avg Hire time as per location
Else Force Date
If any other stage, Force Date + Delay
If Offer Process or Second/Final Round Interviews, R3 Date
If Force Date > R3 Date then Force Date else Greater of Force Date + Delay/R3 Date
My formula - IF(OR($CH38="WFA",$CH38="On Hold"),$AE38+VLOOKUP($CG:$CG,Sheet2!$C$3:$D$6,2,0),IF(NOT(OR($CH38="WFA",$CH38="On Hold")),IF(AND($CJ38<=Sheet2!$A$2,$CI38="Offer Process"),IF($AE38<=Sheet2!$A$2,$AE38+VLOOKUP($AL:$AL,AHT!$A:$I,9,0),IF(AND($CJ38<=Sheet2!$A$2,$CI38="Offer Process"),IF($AE38>Sheet2!$A$2,$AE38,IF(AND($CJ38<=Sheet2!$A$2,NOT($CI38="Offer Process")),$AE38+VLOOKUP($CG:$CG,Sheet2!$C$3:$D$6,2,0),IF(OR($CI38="Offer Process",$CI38="Second/Final Round Interviews"),$CJ38,IF($AE38<$CJ38,MAX($AE38+VLOOKUP($CG:$CG,Sheet2!$C$3:$D$6,2,0)),$AE38)))))))))
I know its a long one but hope i can seek some input..Thanks a ton!! cheers
I have been trying to achieve below result in excel but unable to do so as i am getting 'False' error. Can anybody please help me?
If WFA/On Hold, Force Date + Delay
If R3 Date <= End of Month:
If Offer Process
If Force Date <= End of Month, Force Date + Avg Hire time as per location
Else Force Date
If any other stage, Force Date + Delay
If Offer Process or Second/Final Round Interviews, R3 Date
If Force Date > R3 Date then Force Date else Greater of Force Date + Delay/R3 Date
My formula - IF(OR($CH38="WFA",$CH38="On Hold"),$AE38+VLOOKUP($CG:$CG,Sheet2!$C$3:$D$6,2,0),IF(NOT(OR($CH38="WFA",$CH38="On Hold")),IF(AND($CJ38<=Sheet2!$A$2,$CI38="Offer Process"),IF($AE38<=Sheet2!$A$2,$AE38+VLOOKUP($AL:$AL,AHT!$A:$I,9,0),IF(AND($CJ38<=Sheet2!$A$2,$CI38="Offer Process"),IF($AE38>Sheet2!$A$2,$AE38,IF(AND($CJ38<=Sheet2!$A$2,NOT($CI38="Offer Process")),$AE38+VLOOKUP($CG:$CG,Sheet2!$C$3:$D$6,2,0),IF(OR($CI38="Offer Process",$CI38="Second/Final Round Interviews"),$CJ38,IF($AE38<$CJ38,MAX($AE38+VLOOKUP($CG:$CG,Sheet2!$C$3:$D$6,2,0)),$AE38)))))))))
I know its a long one but hope i can seek some input..Thanks a ton!! cheers