Conditional Formatting - dates

[Guest]

How do I apply conditional formatting to highlight dates that are less than
todays date (today's date should automatically update to the current days
date) Hope this makes sense. It is for purposes of highlighting expired
qualifications. Any advice would be appreciated.

Regards,

 

[bobocat]

try to select the cell
in conditional formating
"Cell Value is" less than =today(), then set format

 

[Guest]

Thanks - this works! How can i get it to ignore the blank cells? Thanks again
for the help.

 

[bobocat]

supposed that the column A contain the date,
Select Column A
condition formating
Formula is =and(a1<today(),isblank(a1)=false))

 

[Bobocat]

Hi Eva,

Can't fully understodd your question.

supposed that in Column A = Date of Course booked ,Column B = Expirey date,
Column C=Remark
if A2 = 2003/12/25,
then at B2 =date(year(a1)+3,month(a2),day(a2) which is 2006/12/25
at C2 =if(b2>=today(), "expired", if(datedif(today(),b2,"MD")>=3, "warning",
""))

If you want cell b2 have conditional formating, then you can set:
"formula is" =c2="warning"

Bobocat

 

[Bobocat]

Did I catch your question?

Bobocat

--
Eva




Thanks alot for this it does make sense, but my date format is 12/11/2006
etc and I have inserted the your suggestions but nothing is happening ,
where
should I be putting in the code , I have set the spreadsheet up to your
examples and the "course start date is in COL A , ROW 2"- I have put the
first code in the formula bar in Col b Row 2 but it still does not return
any
data,have then tried it at the top of COL a in the formula bar, should I
be
putting the formula you have provided by using FORMAT , CONDITIONAL
FORMATTING and have tried both CELL VALUE IS and FORMULA IS but still no
joy
I realise this is basic but I have not used excel for a while and have
been
thrown in the deep end and really appreciate your help



I have formated the cells to date type UK style the version of excel is
2003

 

[bobocat]

sorry, typo error in my formula
at B2 =date(year(a2)+3,month(a2),day(a2) which is 2006/12/25