Conditional Formatting

[Brian from Maui]

Aloha All,

I have in cell A1, the date an employee has completed a training session. I would like to conditional format the cell to prompt (turn yellow) after 11 months, red after 12 months. Now the hard part (for me)is, they may have been trained on say Mar 15th, but the 11 month period starts from the last day of the month, the 31st of Mar. But the date in A1 must reflect the actual date of the completed training. So the dates in A1 will vary, but the 11 month period would always start from the last day of the month.

Brian
This message was edited by Brian from Maui on 2002-04-02 22:10

 

[Brian from Maui]

On 2002-04-03 20:43, Aladin Akyurek wrote:

On 2002-04-03 14:55, Brian from Maui wrote:
Aladin,

It stays red all the time.

Brian

Which date stays red all the time? Do you mean you don't get no yellow while that should be the case?

Any date, tried a few, all red all the time

Brian

 

[Aladin Akyurek]

Any date, tried a few, all red all the time

The target date 9/8/2001 (that is, 8-Sep-01) becomes yellow-backgrounded by the formula for Condition 1:

=DATEDIF(A1,TODAY(),"M")=11

When today's date hits, say, 5/4/02 (4-May-02), the target date's background will turn red and will stay red beyond 5/4/02 as today's date.

Darn it: "Talking times" is hard.

Please elaborate further if I misunderstood the original question.

Aladin

 

[Brian from Maui]

I have in cell A1 a date that an employee has completed training. Recurrent training takes place a year from the last day of the month entered in A1. As an example, 3/02/01 is in A1, however, the date to determine the next training date begins from 3/31/01 and recurent training is to be completed by 3/31/02. So in essence in this case, the employee had one year and 29 days between training dates. Unless the employee completed their training on the last day of the month, they would have one year plus whatever days remaining in the month. Due to company policy the date entered in A1 must reflect the completed training date.
What I plan to do is use the EOMONTH function in another column hide it and reference A1 for the conditional format. Also trying Ian's formula. Been busy lately!

Brian
This message was edited by Brian from Maui on 2002-04-03 23:20

 

[Ian Mac]

Did my answer not do it for you,

If you have trouble veiwing it disable the HTML (I think that's what you do)

Also I THINK the numbers do need to be changed to 11 and 12.
_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-04-04 00:24