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 ]

[Ian Mac]

Untested!

try:

=AND(NOW()>=DATE(YEAR(A1),MONTH(A1)+12,0),NOW()
for the yellow condtion.

and:

=NOW()>=DATE(YEAR(A1),MONTH(A1)+13,0)

Hope this helps.

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-04-03 01:45 ]

[ This Message was edited by: Ian Mac on 2002-04-03 01:46 ]

[The Mad Druid]

I don't know the full solution for this, but perhaps if you tried using some kind of hidden column where it reflected the month in column A but not the actual day. Then used your conditional formatting based from this hidden column rather than the column in view?
The Mad Druid

[Ian Mac]

Brian, for some reason I can't see all of the formula I put for the first part,

I'll try again.

=AND(NOW()>=DATE(YEAR(A1),MONTH(A1)+12,0).....
........,NOW()
_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-04-03 01:48 ]

[Ian Mac]

And again.

can't seem to get this to display, so essential put these two together with an AND function.

=NOW()>=DATE(YEAR(A1),MONTH(A1)+12,0)
=NOW()
also, if you want to highlight the row use:

=AND(NOW()>=DATE(YEAR($A1),MONTH($A1)+12,0),NOW()
(hope the above worked) using the $ on the column you want will anchor the other cells to that reference.

Note: the 12 and 13 may be wrong, could be 11 and 12 respectively.

Hopefully I've worked the < thing out
_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-04-03 02:53 ]

[ This Message was edited by: Ian Mac on 2002-04-03 02:54 ]

[ This Message was edited by: Ian Mac on 2002-04-03 06:33 ]

[Dave Hawley]

Hi Brian


Looks like you may already have your answer but to get the last day of any month within a date you can use:

=EOMONTH(A2,0)

Where A2 holds any date. The EOMONTH is part of the Analysis toolpak, so you may need to go to Tools>Add-ins

[Brian from Maui]

Mahalo Guys,

The time difference here, I just got up. Will try all of these! And it's 4:30 am

Brian

[Aladin Akyurek]

On 2002-04-03 06:29, Brian from Maui wrote:
Mahalo Guys,

The time difference here, I just got up. Will try all of these! And it's 4:30 am

Brian

Not sure but another proposal:

Use for Condition 1:

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

Format yellow.

Use for Condition 2:

=DATEDIF(A1,TODAY(),"M")>=12

Format red.

Note. Conditional formatting will not accept formulas that use EOMONTH, EDATE, etc.

Aladin

[Brian from Maui]

Aladin,

It stays red all the time.

Brian

[Aladin Akyurek]

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?