0

I am trying to do an excel function, either an IF formula or use conditional formatting to do an AND formula.

I need it to produce all three results in one formula, it is:

  1. if the date is before today + 10 days then to mark it blue or "on time"
  2. if the date is before today + 5 days to mark it green or "about to miss"
  3. if the date is after today to mark it red or "over due"

If this is possible, or if there is another way to get the same result, how can I do it?

1
  • You can do this easily with basic conditional formatting. Teaching conditional formatting via a question and answer tho is not really practical. Watch a tutorial or two and you will be able to do this really quickly.
    – Tyson
    Jul 31, 2014 at 12:41

1 Answer 1

0

I use conditional formatting for this purpose. This answer can not possibly give you the step by step for your scenario, but it will get you started.

You will need to modify it according to how your data is laid out, but it should work. There is a tutorial at the source below to help you learn how to do this.

In essence, you are going to use conditional formatting with a formula.

Create a rule and choose Use a formula to determine which cells to format

Below is what it looks like for one that is within 7 days of being overdue.

enter image description here

The formula is cut off in the image. It is =IF(ISBLANK($J2), AND(MEDIAN(TODAY()+1,$E2,TODAY()+7)=$E2),"")

Below is for those that are overdue.

enter image description here

I end up with two rules applied to the same range.

enter image description here

Again, you will have to make this work for your data, but it will work with three conditions. Follow the examples in the source linked below to learn how to do this. It's a great tool I use daily.

Highlight due dates in Excel – Show items due, overdue and completed in different colors

You must log in to answer this question.

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