Today's Date Conditional Formatting

[samsilverman]

I am using Excel 2007.

There is a lot on the forum concerning dates and conditional formatting, but I haven't figured it out for this yet. Please help.

I have two columns of dates. The first column (E) is the date I perform the task. The second column (F) is the date the task is next due.

If a date in column E is within 7 days + or - the current date, I need the text to bold and the cell to fill in yellow.

If a date in column F is the within 2 months in the future or is any past due date based on the current date, I need the text to bold and the cell to fill in green.

Thanks for any assistance you can give.

 

[VoG]

Possibly

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:75px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">F</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:right; ">06/02/2010</td><td style="background-color:#00b050; font-weight:bold; text-align:right; ">31/12/2010</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >E1</td><td >1. / Formula is =ABS(E1-TODAY()<=7)</td><td style="font-weight:bold; font-style:italic; background-color:#ffff00; ">Abc</td></tr><tr><td >F1</td><td >1. / Formula is =OR(F1<TODAY(),F1>E1+60)</td><td style="font-weight:bold; font-style:italic; background-color:#00b050; ">Abc</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

http://www.excel-jeanie-html.de/index.php?f=1

 

[samsilverman]

Sorry for the delay in replying. Here's what finally worked for me.

For the E column where I wanted:
If a date in column E is within 7 days + or - the current date, I need the text to bold and the cell to fill in yellow.

I used:
Conditional Formatting/New Rule/Format Only Cells that Contain/
I chose Cell Value & Between as options
I typed the following in the first formula field
=TODAY()-7
I typed the following in the second formula field
=TODAY()+7


For the F column where I wanted:
If a date in column F is the within 2 months in the future or is any past due date based on the current date, I need the text to bold and the cell to fill in green.

I changed the criteria to 120 days and used:

Conditional Formatting/New Rule/Use a Formula to Determine.../
I then typed the following in the formula field
=F6<TODAY()+120

F6 because it was the first cell affected. I then applied it to a range of cells in the F column.

There's probably another, "cleaner" way, but I'm still a rookie. Thanks VoG for getting me thinking in the right direction.