0

I am trying to creat a formula in excel and cant figure it out. I have 6 colums, they are as follows:

Column 1:Name
Column 2: task performed
Column 3: date performed
Column 4: report number
Column 5: date due
Column 6: status

I would like Column 6 to show "overdue" in red when column 2 is blank. I am trying to make up a formula that will change column 6 to "completed" in green when ANYTHING is written column 2. Can anyone help me make this formula?

4
  • You want conditional formatting. Commented May 10, 2019 at 19:49
  • Ive read that, but im not sure how to get what I need to get out of it
    – terry
    Commented May 10, 2019 at 19:58
  • What have you read?   What part of it do you not understand? Commented May 10, 2019 at 20:15
  • You will need an IF() formula in the cell to return the correct text. And you will need two conditional formatting rules that change the color of the text depending on the outcome. Commented May 10, 2019 at 20:20

2 Answers 2

2

Assume your column names are in cells A1 -> F1. Do the following in cell F2:

  1. Set the following formula - =IF(B2="","Overdue","Completed")
  2. Start a new conditional format, choose "Use a formula to determine which cells to format", and then type the formula - =COUNTIF($F2,"Completed").
  3. Click the format button and choose your green fill or green text.
  4. Start another new conditional format, choose "Use a formula to determine which cells to format", and then type the formula - =COUNTIF($F2,"Overdue").
  5. Click the format button and choose your red fill or red text.
  6. Copy cell F2 and paste it down to all needed rows.
2
  • Great solution! It's important to note that F2 need to be the active cell when starting to create conditional format. Commented May 10, 2019 at 21:06
  • Yes. I had hoped that would be self evident with the short sentence, "Do the following in cell F2", but good point to clarify that. Commented May 10, 2019 at 21:09
0

You can also use the ISBLANK(cell ref) formula to test and show the result you need. For example:

In the Status column =IF(ISBLANK(B2), "Overdue", "Completed")

In the Conditional Formatting rules, the formatting applies where the formula is true. So the rule for the red highlight =ISBLANK(B2) and the rule for the green highlight =NOT(ISBLANK(B2))

Check your absolute and relative cell references in these rules, especially if you expand the selection as it's very easy to get an unexpected result by accidentally testing the wrong cell. If you ensure F2 is the selected cell before writing the rules then you should be fine.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .