EXCEL - Another formular question! Please help!

Question

I have had assistance with the below formular today which is great!

The formula in I2 is as follows:

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

I would like to now have a similar format of COMPLETED, PENDING, EXPIRED in M2, based on the number of days between todays date (A2) and the date that L2 says COMPLETE (date is in H2).

In cell M2, i have the following

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

the only issue is, that if H2 is blank, M2 comes up with "EXPIRED" which i would not like it to do. I essentially want the M column to remain blank, until there is a date in H2.

Any suggestions for this? TIA

Answer 1

Hi,

I've made a few changes to your formula which make it shorter and, I think, correct.

=IF(H2="","",IF(AND(L2="",A2-H2>42),"EXPIRED",IF(ISNUMBER(L2),"COMPLETED","PENDING")))

H2="" is the same as ISBLANK(H2) same for L2=""

I've changed (A2-H2>42) to A2-H2>42

Answer 2

Hi respected Sarah Kennedy1,

I'm writing a follow up this case, and I haven’t received any information from you, may I know have you checked above reply? Feel free to post back if you need further assistance.

Have a good day and stay safe 😊

Sincerely,

Waqas Muhammad

Answer 3

Dear respected Sarah Kennedy1,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, you can modify the formula by adding an extra condition to check if H2 before evaluating the other conditions. 

The first condition ISBLANK(H2) checks if cell H2 is blank. If it is blank, it will return an empty string "", which means the cell will remain blank. If H2 is not blank, it will proceed to the next conditions.

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

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad