Conditonal Formatting 1

[Superbaker]

I currently have a spreasheet where I used conditional formatting to hightlight dates that are older than the current date. The spreadsheet keeps track of insurance certificates and any certificates that have expired.

It says:

if cell value is less than =NOW() turn the text red.

This has been working fine but now they want to have the certifcates change color 1 month before they are up. I tried adding change it to

if cell value is less than =NOW()+30 turn the text red.

This didnt seem to work. Any ideas on how to make this happen

Thanks

 

[DaleWatson123321]

Superbaker,

This will do the job...

Change Condition to "Formula is", and use this formula
=NOW()-C5>=30
...where C5 is the current cell.

Please advise as to how this works out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Superbaker]

Will this apply to the whole spreadsheet, or just the current cell. I need it to apply to the entire spreadsheet since there are many dates all over the spreadsheet

 

[DaleWatson123321]

It applies to the current cell, but you can then copy the cell to the entire spreadsheet, or to whatever portion where it's required.

To ensure you don't over-write any data you might have, you should Copy the one cell, then select the destination range where you want to copy it, and then use: Edit - Paste Special - Formats.

Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Superbaker]

I couldn't get that suggestion to work the way I want. I figured out how to do it. I first had to make sure the orignal conditional formatting was take off before applying the new formatting. I hightlighted the entire sheet and went to conditional formating under the Format tab. I did

Cell value is less than =NOW()+30 turns the text red.

When I tried this before, I didn't have the old conditional formatting taken off so it was applying both so it wasn't coming out the way I wanted it to.

 

[DaleWatson123321]

Superbaker,

I'll agree with your formula, but ONLY if you agree that you made a "typo".

Shouldn't your formula be...
Cell Value is Less than =NOW()-30

-i.e. a MINUS character instead of a PLUS character ?

Additional suggestion... If you want the cells to be more noticeable, you could ALSO choose to use Format - Patterns, and choose a bright BACKGROUND Color - like Bright Yellow.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Superbaker]

It works with =NOW()+30. Maybe I didn't explain what I wanted to do well enought. I wanted all certificate dates to be hightlighted 30 days before they are due. Example: If a certificate expires 4/2/2003. I want it to be hight lighted 1 month before that so that would be 3/3/2003. So I add 30 to NOW which brings me to 4/3/2002 and any date before then, will be hightlighted. I probably didn't explain it right in the inital post.

Thanks

 

[DaleWatson123321]

Superbaker,

I'm pleased you were able to get it to work.

Would you be interested in a method of "automatically" doing the following"...

1) Have a formula that tells you instantly the number of records that meet your criteria of expiring in 30 days ?

2) Have a macro button, which when clicked, will instantly extract all the certificate data that you require for those records expiring in 30 days. A copy of this data would be instantly extracted to a separate sheet, and formatted for printing. All you would need to do, is to hit the &quot;Print&quot; icon, or use <Control> P <Enter> after viewing the data.

If you're interested, email me, and I can then email you a file that will demonstrate the above.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[PeterMoran]

Hi Superbaker & Dale,

It seems to me that if you need those certificates which are due in the next 30 days to appear as red then you need a formula which defines the range limits which are to apply such as:

=AND(B2-TODAY()>-1,B2-TODAY()<31)

in cell B2. (This says if the date in B2 is > yesterday's date and less than today's date plus 31 days then it will show as red)

Otherwise all old certificates will continue to display as red unless they are removed once they have been processed.

Your final ideas are great Dale, as usual, anticipating the next questions!!

Good Luck!

Peter Moran
Two heads are always better than one!!