Conditional formatting excel 2007 -certificate expiration dates
Hi there
Happy to be part of this group!
I have small request regarding conditional formatting in excel 2007.
I have trouble with formatting attached sheet in order to show expiration dates in two different colors
For 3 months before expire in YELLOW
For 1 month Before expire in RED
Thanks for help
for 1 month - red use
=AND(D1>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),D1<=TODAY())
that tests if within today and 1 month ago
for 3 month - yellow
=AND(D1>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),D1<=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
this tests for between 3 months and 1 month
in you example most of the dates are over three months anyway
did you want to flag any date that is less then 1 month red
and less than 3 months yellow
if so
Yellow
=AND(D1<>"",D1<=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))
RED
=AND(D1<=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),D1<>"")
PUT YELLOW first and stop if true
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thanks you Etaf ...That was fast
Acctualy Etaf this is not working properly because even if date is 2012 is marking YELLOW,as soon I change date to 2014 is gone all collor??
Anybody?
This may be better
Red for anydate upto a month in future
Yellow for dates a month in future to three months in future
see attached
yellow
=AND(D1<=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),D1>=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())))
red
=D1<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY()))
thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks