Conditional Formatting relating to dates

[Larph]

Hi there


I'm attempting to set up a record of contracts via excel.


I'd like to implement a cell that serves as a reminder for whether or not action should be taken depending on the date and end of contract date (ie more than 12 months left = green, 12 to 9 months left = amber, 9 - 6 months left = red).


Any ideas as to how I can build a formula that will look at the end contract date and determine conditional formatting for the cell U6?


Much appreciated, my file is located here: http://www.mediafire.com/file/da69p246kvf63e4/Contracts%20v4.5.xls


Also, if anyone has any suggestions for sexing this document up, I'm more than happy to entertain them!


Cheers.

 

[Hui]

Larph

You will need 3 conditional formats

Select the range U6:U69

Select Conditional Format

Manage Rules

Add a New Rule and Use a Formula to Determine which cells to format

Use a Formula of =I6>(EDATE(TODAY(),12)) and set format Green

add 2 more Conditional Formats

=I6>(EDATE(TODAY(),9)) and set format Amber

=I6>(EDATE(TODAY(),6)) and set format Red

Rearrange the Conditional Formats so that the Green is at Top and Red at Bottom

Set Stop = True to Ticked for Green and Amber conditional formats

That should be it...

 

[Larph]

Hui - that is an excellent solution, thank you for your assistance.


I did manage a long winded work-around but this is a far better result. Thank you.

 

[Hui]

I noted after I posted that you have a pre 2007 file.

The instructions won't work word for word but the general intent will still make it work, just the menus will look diferent

 

[Larph]

Hi Hui


I've implemented your suggestion and it seems to work well apart from a couple of rows where it doesn't seem to register.


I've checked the conditional formatting formula and made sure that the formula relates to the end dates, but there are a few odd ones there that won't register. Any ideas?


(For example, row 21).


http://www.mediafire.com/file/4x8wgfd3n3mdq3n/Contracts%20v5.5.xls