# Thread: IF statement not working - I'm using .xlsx file

1. ## Also - conditional formatting

I wanted to add this image as well, for some reason 2 of the 4 conditional formatting rules are not working? Status (yrs), H works off of Days Left, G. 2 of the formats that I setup are not working, yet the formula is correct in all cells and the same value in the conditional rule.

2. Originally Posted by bobjglover@gmail.com
the challenge with this is I am unable to call out "between"? for instances I am hoping to identify contracts that are 30 days and under as" Very Near/Yellow" and 30-90 days "Getting Close/Orange" and the remainder over 90 "Ok/Green"?
Try the formula on numbers from 0 to 40 and you'll find that 'between' is catered for:

A workbook rather than pictures of a workbook is infinitely preferable.

3. Originally Posted by p45cal
This is equivalent to your existing formula:
=INDEX({"Expired","Soon","OK"},MATCH(G12,{0,1,31}))
You can extend it thus:
=INDEX({"Expired","Very Near expiry","Getting close","11 days to go","about 16 days","3 weeks","26 days","OK"},MATCH(G12,{0,1,6,11,16,21,26,31}))
I know this is incorrect but I need to add in between for example - =INDEX({"Expired","30-","30+","60+"},MATCH(G12,{0,<=30,>30,=>60})) can you adjust?

4. Originally Posted by p45cal
Ok, thank you this handles 99%, I would like to account for any contracts that have expired and gone past the "0" mark and into Negative days? I tried to add "-0" in the MATCH but clearly doesn't work. and lastly if there are no beginning or End dates that the cell shows blank until the fields are populated.

5. see attached.

6. ## P45 - Frig gin genius!

Originally Posted by p45cal
see attached.
OUTSTANDING! thanks so much for workin it until complete!

Page 2 of 2 First 1 2

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•