1

I have an Excel file that links to some documents, certificates and such, which are valid for a year. For example, since Feb 20, 2014 until Feb 20, 2015.

To the right, I have a column in which are the dates of expiry, and next to it a column that is a month earlier - when I should start thinking about contacting the 3rd party to send us the new certificate for the following year.

Is there a way to make a date cell in excel be conditionally formatted (for example red background with while letters) depending on the date today? For example, in that second column, all dates from Jan 20, 2015 until todays should be coloured red.

Is that possible and how would one go about that?

Improve this question
2
  • Can anyone give an explanation is there something here that would account for the downvote?
    – Rook
    Feb 26, 2015 at 13:44
  • Although I did not down vote this, my guess is it is for lack of research effort or showing what you have already attempted.
    – CharlieRB
    Feb 26, 2015 at 13:50

1 Answer 1

Reset to default
1

This is an answer I gave for a previous question. Please realize it 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

Improve this answer
1
  • FYI to OP: The formula for the date one month prior to today is DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())). There's also the EDATE function but it may or may not work on your version. EDATE(TODAY(),-1)
    – Engineer Toast
    Feb 27, 2015 at 4:29

You must log in to answer this question.

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