Conditional Formatting

**Lorna B** · 05-31-2012, 09:19 AM

Hi All

Please find attached an example workbook.

In Cell range c4: K22 you will find a list of dates which are the last course attended by the colleage's .

in cells C2: K2 you will find how long the certificate is valid for.

if the certificate has expired i would like the Cell range C2: K22 to highligh red.

im unsure where to start with this i have looked through the Rules for conditional formatting but I can not see a which one to choose.

Is this possible? to just enter the formula in the rule??

I would like the cell to go amber when the expiry date is within 3 months and I would like it to go red if it has passed.

Thanks

**Pauleyb** · 05-31-2012, 10:30 AM

Removed the files from the two previous posts, they were identical and used a lesser month adder calculation.

Can't seem to find the magic button to delete posts, otherwise I'd remove the two above...

Pauley

**Lorna B** · 05-31-2012, 10:44 AM

Originally Posted by Pauleyb

I think this does what you ask for. Always hard to describe the solution for conditional formatting, so my advice is to open up the conditional formatting and see what was done.

One other 'trick' I used was to change the format of the months to integers with the text 'Months' appended. This let me use the numbers in the conditional formatting equation while still looking like your original.

Finally, the cells with 'dates' set as '03.06.11' are not formatting because they are not in the date format - they are text. You will need to fix that if you wish.

Pauley

Can i just check with the conditional formatting - i have had a look but am i right in in Cell j18 if i put a date 30/06/2011 it should come up amber as the this falls within 3 months of being expired but not expired.

if i enter this into your worksheet it comes up red - but it hasnt expired as it is valid for 12 months (as per cell J2)

Thanks

Lorna

**Lorna B** · 05-31-2012, 10:48 AM

Originally Posted by Lorna B

Can i just check with the conditional formatting - i have had a look but am i right in in Cell j18 if i put a date 30/06/2011 it should come up amber as the this falls within 3 months of being expired but not expired.

if i enter this into your worksheet it comes up red - but it hasnt expired as it is valid for 12 months (as per cell J2)

Thanks

Lorna

Posted this before i seen your amendment - just looking now.

Thanks

**arlu1201** · 05-31-2012, 02:01 PM

Originally Posted by Pauleyb

Removed the files from the two previous posts, they were identical and used a lesser month adder calculation.

Can't seem to find the magic button to delete posts, otherwise I'd remove the two above...

Pauley

Removed the posts on the direction of the user.