Conditional Formatting

Hi,

I have a spreadsheet which contains a list of dates when training was completed and the date each course expires. The expiry is shown as a number od days (eg 3 years is 365*3)

What I am trying to do is apply Conditional Formatting whereby the cell will turn red when the date shown as completed has exceeded the renewal period. That is, with a completed date of 19 August 2013 and an expiry date of +1095 days (3 years), how do I get the cell to show red when the expiry date is greater than 19 August 2013 + 1095 days (3 years)?

I have been going around in circles with this one and I will be grateful for your help.

Thanks

Stuart

=A1>(your_date+your_days)

Originally Posted by sandy666

=A1>(your_date+your_days)

Thank you for your speedy reply.

In your reply, will cell A1 be todays date?

I don't know what should be in A1 it's up to you
format should be like this (or similar): =A1>DATE(2013,08,119)+1095 if you want A1 colored.
check it with less days, like: A1>DATE(2017,03,10)+2 and type in A1: 15/03/2017 then change to 11/03/2017

You've Excel 2010 so change fiile format to xlsx and reattach again

Originally Posted by sandy666

You've Excel 2010 so change fiile format to xlsx and reattach again

I'm at work and use 2003.....I know ancient or what

All cells with dates should be red except last: 20/03/2016 20/03/2019
=TODAY()>($B3+1095)
is that what you want?

I worked out this formula =$B$3+$B$2<$A$2, where $B$3 is the date the course was completed, $B$2 is 1095 and $A$2 is TODAY()

This seems to work and I think it is as you have shown in your last reply.

The question now is how do I apply this to the whole column. Must I input the formula for each cell for the Conditional Formatting to work? I do hope not.........

Thanks again for your help with this.

which column?
btw. all cells in column from start to an end??? or any range?

Looking at your example, it differs from what I did in that I did not include a result date (that is, Date Completed + 1095. I did this in my formula).

I want to add the Conditional Formatting to the column with all my Date Completed data. I have tried to use B3:B9 but it didn't work.

I think we are getting there. Thanks again.

Thank you for your assistance with this. I think with your help the issue is resolved.

On the same spreadsheet I know have a similar problem.

My spreadsheet (attached to this message) contains COUNTIF and some Conditional Formatting.

At Rows 29 and 30 the COUNTIF formula calculates the number of courses passed. However sometimes this will go out of date and should not be shown in this total.

I tried this but it doesn’t work

=COUNTIF(B4:B16,">0") AND (TODAY()>B4:B16+$B$2)

How do I set a formula which will only count those courses which are in date?

Grateful for any help.

Originally Posted by sandy666

try:
B29: =COUNTIF($B$4:$B$16,">"&SUMPRODUCT($B$4:$B$16*(TODAY()>($B$4:$B$16+$B$2))))
B30: =COUNTIF($B$19:$B$27,">"&SUMPRODUCT($B$19:$B$27*(TODAY()>($B$19:$B$27+$B$2))))

Excellent!

Thank you so much. Without your help I would never have been able to get this right.........I haven't even heard of &SUMPRODUCT....

Thank you.

You are welcome
Thanks for rep and marking thread as solved.

Additional solution:
If you want you can use
B29: =IF(ISNUMBER(B$2),COUNTIF(B$4:B$16,">"&SUMPRODUCT(B$4:B$16*(TODAY()>(B$4:B$16+B$2)))),COUNTIF(B$4:B$16,">0"))
B30: =IF(ISNUMBER(B$2),COUNTIF(B$19:B$27,">"&SUMPRODUCT(B$19:B$27*(TODAY()>(B$19:B$27+B$2)))),COUNTIF(B$19:B$27,">0"))
and drag both to the right