Conditional Formatting

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Untested!

try:

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

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
 
Upvote 0
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
 
Upvote 0
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()<DATE(YEAR(A1),MONTH(A1)+13,0))

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-04-03 01:48
 
Upvote 0
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()<DATE(YEAR(A1),MONTH(A1)+13,0)

also, if you want to highlight the row use:

=AND(NOW()>=DATE(YEAR($A1),MONTH($A1)+12,0),NOW()<DATE(YEAR($A1),MONTH($A1)+13,0))

(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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top