Hi Niki,
I've tested this so you should be okay.
Assume your dates are in column A.
Select your dates in col A
Click Format Conditional Formatting
Make these settings:
Condition 1
Formula Is |
=(AND(TODAY()=DATE(YEAR(A1),MONTH(A1),1),TODAY()< (DATE(YEAR(A1),MONTH(A1)+1,1)-1)))
Click Format button Patterns tab Yellow? OK
Click Add (this adds condition 2)
Condition 2
Formula Is | =TODAY()=DATE(YEAR(A1),MONTH(A1),1)-1
Click Format button Patterns tab Red? OK
Done.
Condition 1 checks to see if today's date is greater than the first day of
this the month referenced in Col A AND less than the last day of the month
referenced in Col A.
Condition 2 checks to see if today's date is greater than the last day of
the prior month referenced in Col A.
I hope this helps.
Best regards,
Kevin
"Niki" wrote in message
...
Dear Max: I jumped for joy when I saw this thread. Alas, following the
directions exactly - I cannot get it to work. I am tracking expiration
dates
also. Have formatted my dates as 1/1/2005 - it just ignores all the
formatting I do. What could possibly be wrong. Appreciate any feedback
at
all.
Niki
"Max" wrote:
Perhaps try this ..
Assuming your due dates are listed in col B,
header in row1, dates in B2 down
Due date
31-Aug-04
12-Sep-04
01-Dec-04
etc
Select col B
Click Format Conditional Formatting
Make these settings:
Condition 1
Formula Is | =B2=""
Leave it as "No format set"
Click Add (this adds condition 2)
Condition 2
Formula Is | =MONTH(TODAY())=MONTH(B2)
Click Format button Patterns tab Yellow? OK
Click Add (this adds condition 3)
Condition 3
Formula Is | =TODAY()B2
Click Format button Patterns tab Pale red? OK
Click OK at the main dialog
--
Note that you can also simultaneously format
the font colour/bold (in the Font tab), etc
besides just formatting the fill colour in the Patterns tab
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----
"canman" wrote in message
...
I'm a Newbie that's been missiled into a job that requires many
spreadsheets to track recertification dates. The sheets are no problem
but I'd like to be able to make due date cells change colour to yellow
if in month of recert and red if past recert date.
Doable????? thanks for any input
--
canman
------------------------------------------------------------------------
canman's Profile:
http://www.excelforum.com/member.php...o&userid=14596
View this thread:
http://www.excelforum.com/showthread...hreadid=262235