Nested IF and OR

[loumar1]

Hi everyone

I have successfully created a nested IF with AND statement, but realized after completion that I forgot to check for a new variable.

When I try to add the new variable  into the equation it keeps failing

The working formula looks for SEVERITY, Prime, Off Shift, and Total Onsite Time., then calculate whether service teck onsite miss

I am trying to add new variable - onsite date

Here is the working formula, which outputs either Y or N for missed onsite time, refer to column AJ

=IF(AND(E2=1,AB2="Prime",AI2<=2),"N",IF(AND(E2=1,AB2="Prime",AI2>2),"Y",IF(AND(E2=1,AB2="Off Shift",AI2<=3),"N",IF(AND(E2=1,AB2="Off Shift",AI2>=3),"Y",
IF(AND(E2=2,AB2="Prime",AI2<=4),"N",IF(AND(E2=2,AB2="Prime",AI2>=4),"Y",IF(AND(E2=2,AB2="Off Shift",AI2<=8),"N",IF(AND(E2=2,AB2="Off Shift",AI2>=8),"Y",
IF(AND(E2=3,AB2="Prime",AI2<=24),"N",IF(AND(E2=3,AB2="Prime",AI2>=24),"Y",IF(AND(E2=3,AB2="Off Shift",AI2<=24),"N",IF(AND(E2=3,AB2="Off Shift",AI2>=24),"Y",
IF(AND(E2=4,AB2="Prime",AI2<=99),"N",IF(AND(E2=4,AB2="Prime",AI2>=99),"Y",IF(AND(E2=4,AB2="Off Shift",AI2<=99),"N",IF(AND(E2=4,AB2="Off Shift",AI2>=99),"Y"))))))))))))))))

Colunmn X contains the variable that I forget to initially check for ---- onsite service technician date.

Not all calls require service teck to go onsite, so if the service teck onsite date in column X shows 1/0/1900, this means that service never had to go onsite to resolve the issue, therefore there could not be a missed onsite time, Calls flagged with onsite date 1/0/1900 automatically should have the missed onsite time flag in column AJ set to N.

Can anyone assist in updating my formula above to include a check for column X, to see if onsite date is equal to 1/0/1900 ?

I tried adding this check but the formula fails.

The logic should be to check for this onsite date first, and if the date is not 1/0/1900, then proceed with the remaining logic in the formula. If the check for 1/0/1900 finds a match, the mark column AJ as N,

I have attached a test file

Please disregard the current formulas in column AJ, in the excel file as I had to manually process each line to get the right output.

Louis

[#2 · Accepted solution]

@loumar1 Since the numerical value for the date January 0, 1900 equals to zero, you can wrap your entire formula in one more IF statement.

=IF( X2=0, "Not applicable" , <your formula with the = sign> )

[#3]

Thank you very much, I just tried your recommendation and it worked.
I thought I had tried your suggestion previously, but was getting errors

Thank you