Forum Discussion
HELP required with formula or conditional formatting....
Hi all, hoping that i can get some good advice and hopefully an answer to my problem.
I have a spreadsheet, picture attached, I already have a few formula and conditional formatting rules applied.
I would like to change 'column I' to one of 3 values, based on the date values in columns D and E.
If both dates have passed, then D&E auto change to green, Todays date stays white, dates in the future are yellow, and TBC (to be confirmed) are red.
In Column I, these are reflected by "complete", "Pending" and "incomplete". I have to manually change these at the moment (using the data validation list).
My question is, is there a way to change the status in column I automatically, based on the values in columns D and E.
So far I have avoided any macros, which am hoping to avoid with this also.
Any help would be very much appreciated.
In your screenshot, the data began in row 7. In your workbook, they begin in row 4, so the formula has to be adjusted accordingly. In the following formula for I4, I also took empty cells into account:
=IF(AND(D4="",E4=""),"",IF(OR(D4="TBC",E4="TBC"),"Incomplete",IF(OR(D4>TODAY(),E4>TODAY()),"Pending","Complete")))
9 Replies
You haven't specified under which conditions the status should be N/A or Cancelled. Here is a first attempt. It will probably have to be modified/expanded. In I7:
=IF(OR(D7="TBC",E7="TBC"),"Incomplete",IF(OR(D7>TODAY(),E7>TODAY()),"Pending","Complete"))
Fill down.
HansVogelaar Hi Hans,
I tried that formula and unfortunately the value changed in I7 to "Complete" every time, despite the value entered in D and E.
In the I column, there has to be a rule order,1. if either cell in the D and E columns has a "TBC" then the I cell should be "incomplete",
2. if either of the D and E columns has a date in the future (yellow) then the I cell should read "Pending"
3. Only if both D and E are dates that have passed or todays date then I cell should read "Complete"I have attached a copy of the work sheet to see if that is of any help to you.
Regards
In your screenshot, the data began in row 7. In your workbook, they begin in row 4, so the formula has to be adjusted accordingly. In the following formula for I4, I also took empty cells into account:
=IF(AND(D4="",E4=""),"",IF(OR(D4="TBC",E4="TBC"),"Incomplete",IF(OR(D4>TODAY(),E4>TODAY()),"Pending","Complete")))
