Help with displaying if dates are overdue

**rhysp** · 02-04-2013, 10:52 AM

Hello

I am working on a spreadsheet that lists completed training courses. Some of these courses require you to go on a refresher course every few years.

What I would like if possible is for the cells tu turn red once the course is overdue, or possibly within 1 month of being overdue, and ideally keep it on one sheet and not use another for referencing.

For example if A1 is the date the course was completed and it needs to be re-done every 3 years. Say it was completed on 1/2/13 I would like it to remain black, however once it is 2/2/2016 I would like it to turn red.

Is there a way to do this with conditional formating or an IF formula? Or would I need to recreate the spreadsheet on a 2nd sheet, list all the due dates for refreshers courses and use an IF formula to change the colour on the first sheet if it goes past that date.

I realise that the 2nd option is probably easier but I would like to make it so that you only need to change the one date so that anyone can take over the spreadsheet and I wont have to worry about them not changing all the dates and losing track of whats due when.

Many thanks

Rhys

**Pepe Le Mokko** · 02-04-2013, 11:07 AM

Hi
the question is to know if all your courses must be refreshed after three years. Are there instances where this interval is different (1 year, 6months) depending on the course?

**rhysp** · 02-04-2013, 11:13 AM

Hi

Yes the refresher dates are different for various courses.

The spreadsheet is laid out so that names are in column A, then each column after is a different course.

So Row 1 for example would contain all course completion dates for one person, Row 2 the next person etc.

Rhys

**MarvinP** · 02-04-2013, 11:44 AM

Hi rhysp and welcome to the forum,

You need to add some information somewhere about how long before a renewal test is needed. See the attached showing how I might do this problem. I could do a Pivot Table from my data to make it look similar to yours or just leave it like it is and deal with it like that.

**rhysp** · 02-04-2013, 11:53 AM

Hi MarvinP

That is along the lines of what I would like.

Do you know if the following would be possible.

If we use yours as a refernce.

Ideally I would like Cell C2 to be the cell that changes. So, if I add a new column at the end of the spreadsheet with the renewal years, for this scenario I'll say its D2, then have E2 work out what C2 plus E2 is, and then depending on whether C2 is before or after the date in E2, have it stay the same or change colour.

Does this make sense to you?

Rhys

**MarvinP** · 02-04-2013, 11:59 AM

Hi rhysp,

I think that is what my example does. If you change the dates in column C it uses the columns of D,E,F for the Years, Months and Days the test is good for. If it is past due then column G is background as a conditional format.

I added columns E and F because I wanted a test to be good for a year and a half or partial years.