nested if statement

[Wandy]

i have this issue
if (C2="annual check") {
if (D2<TODAY() print expired;
else if (D2<=TODAY()+7 print expiring soon;
else print ok;
} else if (C2="monthly check") {
if (D2<TODAY() print expired;
else if (D2<=TODAY()+7 print expiring soon;
else print ok;
}

i can work fine with "annual check" alone
but when combining with "monthly check" i have no clue how to write it.
i hope someone can help on this. Thanks in advance.

 

[shamimarita]

Hello Wandy,

You can combine Annual Check and Monthly Check using the OR function, since both use the same conditions for the date in D2.

=IF(D2="","",IF(OR(C2="annual check",C2="monthly check"),IF(D2<TODAY(),"Expired",IF(D2<=TODAY()+7,"Expiring Soon","OK")),""))

This formula will return:

• Expired → if the date in D2 is before today.
• Expiring Soon → if the date is today or within the next 7 days.
• OK → if the date is more than 7 days away.
• Blank → if D2 is empty or C2 is neither "annual check" nor "monthly check".

Since the conditions are identical for both check types, using OR avoids repeating the same nested IF statements.