Excel Formula Help please :)

Question

What can I add to the formula below (currenlty in N2), so that if J2 says 'COMPLETE', N2 will say 'PENDING' - without losing the remainder of what i have there already.

current formula is:

=IF(ISBLANK(L2),"", IF(AND(ISBLANK(M2), (A2-L2 > 28)), "EXPIRED", IF(ISNUMBER(M2), "COMPLETED", "PENDING")))

Answer 1

Try this one.

=IF(AND(K2="",J2="COMPLETED"),"PENDING",IF(ISBLANK(L2),"", IF(AND(ISBLANK(M2), (A2-L2 > 28)), "EXPIRED", IF(ISNUMBER(M2), "COMPLETED", "PENDING"))))

This formula means,

If K2="" and J2 = Completed, then N2 should be Pending
Plus your previous formula.
=IF(ISBLANK(L2),"", IF(AND(ISBLANK(M2), (A2-L2 > 28)), "EXPIRED", IF(ISNUMBER(M2), "COMPLETED", "PENDING")))

Answer 2

Thanks - it is still doing the same thing though - changing column N2 to PENDING even when there is a date in L2 and M2 (which should change N2 to COMPLETED).

Answer 3

Try this one.

=IF(ISBLANK(L2),"", IF(AND(ISBLANK(M2), (A2-L2 > 28)), "EXPIRED", IF(ISNUMBER(M2), IF(J2="COMPLETED","PENDING","COMPLETED"), "PENDING")))

If it still doesn't meet your goal, share us each result with all the conditions. We may rewrite your entire formula.

Answer 4

Hi thanks for the reply - unfortunately that hasn't worked, when i put this in, it seems to be overriding the existing formula that changes cell N based on dates in cells L and M.

I need the settings in this formula

=IF(ISBLANK(L2),"", IF(AND(ISBLANK(M2), (A2-L2 > 28)), "EXPIRED", IF(ISNUMBER(M2), "COMPLETED", "PENDING")))

PLUS

OR if J2 says COMPLETED, but there is no date in k2, N2 needs to say PENDING

Answer 5

=IF(J2="COMPLETED","PENDING",IF(ISBLANK(L2),"", IF(AND(ISBLANK(M2), (A2-L2 > 28)), "EXPIRED", IF(ISNUMBER(M2), "COMPLETED", "PENDING"))))