Forum Discussion
HELP required with formula or conditional formatting....
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")))
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")))
HansVogelaar Slight issue that I did not foresee, I will need the following added to the formula,
If D or E column have PAO or N/A in it, then column I should read complete or incomplete based on the date in the other column..... As per attachment.......Help again please?
