If function with 3 conditions

[CarolB]

Hi

I wanted a formula and conditioning formatting for 3 conditions,

If training is past expiry date to go red and say expired, If not due stay green and say not due. if 30 days or less to go say - 30 day or less and turn orange.

Thanks in advance and any help will be appreciated.

 

[mole999]

where is your reference date

you might start with

=IF(C2>TODAY(),"Not Due",IF(C2 < TODAY(),"Expired",""))<today(),"expired",""))

as Formula, you can then add conditional format to the Expired and Not Due Cells</today(),"expired",""))

 

[CarolB]

Hi

Is it not possible t have a 3rd condition such as if its 30 day or less - state due in30 days or less.

thank you

 

[Joe4]

Try this:

=IF(TODAY()>C2,"Expired",IF(C2-TODAY()<=30,"30 days or less","Not due"))

 

[Joe4]

For Conditional Formatting, your three CF formula rules would look like:

Rule 1
=C2 < TODAY()
Format red

Rule 2
=AND(C2 >= TODAY(), C2 - TODAY() < = 30)
Format orange

Rule 3
=C2 - TODAY() > 30
Format green

 

[CarolB]

Hi

Thank you very much, that I exactly what I required. I had two conditions working but not all three

 

[Joe4]

You are welcome. If you want to read up more on nesting IF statements, see here:
https://support.office.com/en-us/article/if-function-%E2%80%93-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8

 

[CarolB]

Hi

I now have two additional scenarios to complicate matters, can I add something if cells are blank or that they have N/A in addition to the ones you sorted above.

Many Thanks

 

[CarolB]

is it possible to now add 2 further conditions. I.E Cell is going to be blank or have N/A. Then make the answer to both N/A.

Thanks in advance
Many Thanks

 

[Joe4]

Sure, just nest another level in, i.e.

=IF(OR(ISNA(C2),C2=""),"N/A",[COLOR=#333333]IF(TODAY()>C2,"Expired",IF(C2-TODAY()<=30,"30 days or less","Not due")[/COLOR])