conditional Formatting

[monitor10729]

I am trying to do a conditional format for 3 conditions.

The first one:

For the cell to change to red if the due date is today or greater than today

The second one

For the cell to change to yellow if the due date is within 60 days.

The third one

For the cell to change to green if it is greater than 60 days of the due date.

The cells that I have so far are "Training completed in column (C)
Due Dates in column (D)

I have A formula already set up in the column due (D column) to automatically give me the training due date.

for example if someone completed their training on 14 Oct 05 (cell C6), I have a formula to calculate the due date in (cell D6). In this case the training is due 15 months from the completed date (14 Oct 05). My formula in D6 is (c6+540), to calculate training due 540 days from the completed date which comes out to 07 Apr 07.
any help appreciated.

My question is how do I set up the three conditional formats mentioned above or should I go about it a different way?

 

[titch]

I'm using Excel2000 but this should apply to other versions too...

I am assuming that you want the Due Date to be red if the due date is today or has already passed (less than today).

Condition 1:
Cell Value is less than or equal to =TODAY() - set your formatting options as you require.

Condition 2:
Cell Value is less than or equal to =TODAY()+60 - set your formatting options as you require.

Condition 3:
Cell Value is greater than =TODAY()+60 - set your formatting options as you require.

Enter the above conditions into the first cell and then use copy - paste special (formats) to paste the conditioanal formatting to other cells.

Hope this is what you need - let me know how you get on...

 

[monitor10729]

Thanks for your help, I'll try it soon but does the world "TODAY" refer to the date that I already have in the box. For example say that I want to set a box yellow that is due 60 days before Oct 2006 and not 60 days from today. Would I use the same formula?

 

[titch]

=TODAY() is the formula that represents today - therefore =TODAY()+60 is the date in 60 days time

If today is 2nd November 2005 then =TODAY()+60 will be 1st January 2006

If you wanted to change this to represent October 2006 you need to use the excel date value in the formula instead of TODAY().

To highlight the date if it is 60 days before 1st October 2006 - replace =TODAY()+60 with =38991-60

However why not use the date value 38931 instead (38991-60) which represents the date 2nd August 2006?

Hope this helps...?