Thanks:  0
Likes:  0

1. 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 ]

2. 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 ]

3. 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?

4. 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 ]

5. 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 ]

6. 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

7. Mahalo Guys,

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

Brian

8. 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.

It stays red all the time.

Brian

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

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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•