Conditional Formatting Formula Question

[Aimee S.]

Hi folks!

So for my entire worksheet and all data therin I want to use conditional formatting with Formula to make any data that has been updated within seven days (either in the past or future) of =TODAY() to be bold. How would I write that?

All the data contained in this sheet is in either mm/dd/yy or % format. Oh and I use Excel '10 if that helps.

 

[GlennUK]

And are you keeping date stamps for all your data then?

 

[Aimee S.]

The sheet sort of looks like this. The percentages to the right need to stay in % format and the dates in mm/dd/yy.

<TABLE style="WIDTH: 432pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=577><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 48pt" height=64><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #b8cce4; WIDTH: 203pt; HEIGHT: 48pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl73 height=64 width=271 colSpan=4>OSP - Engineering</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #b8cce4; WIDTH: 61pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl86 width=81>Environ-
mentals</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #b8cce4; WIDTH: 168pt; BORDER-TOP: black 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl73 width=225 colSpan=3> OSP - Contract</TD></TR><TR style="HEIGHT: 89.25pt; mso-height-source: userset" height=119><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b8cce4; WIDTH: 35pt; HEIGHT: 89.25pt; BORDER-TOP: black; BORDER-RIGHT: windowtext 1pt solid" class=xl81 height=119 width=46>Route Miles</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=75>Planned </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: windowtext 1pt solid" class=xl83 width=75>Actual </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=75>% Complete</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 61pt; BORDER-TOP: black; BORDER-RIGHT: windowtext 1pt solid" class=xl83 width=81>Actual </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #b8cce4; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: windowtext 1pt solid" class=xl84 width=75>Planned </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: windowtext 1pt solid" class=xl83 width=75>Actual </TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; WIDTH: 56pt; BORDER-TOP: black; BORDER-RIGHT: black 1pt solid" class=xl85 width=75>% Complete</TD></TR><TR style="HEIGHT: 32.1pt; mso-height-source: userset" height=42><TD style="BORDER-BOTTOM: #a6a6a6 1.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 32.1pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 1pt solid" class=xl79 height=42>17</TD><TD style="BORDER-BOTTOM: #a6a6a6 1.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #969696 0.5pt solid" class=xl80>6/15/12</TD><TD style="BORDER-BOTTOM: #a6a6a6 1.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl76> </TD><TD style="BORDER-BOTTOM: #a6a6a6 1.5pt solid; BORDER-LEFT: #969696 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl78>20%</TD><TD style="BORDER-BOTTOM: #a6a6a6 1.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 1pt solid" class=xl77> </TD><TD style="BORDER-BOTTOM: #a6a6a6 1.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #969696 0.5pt solid" class=xl80>10/24/12</TD><TD style="BORDER-BOTTOM: #a6a6a6 1.5pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl76> </TD><TD style="BORDER-BOTTOM: #a6a6a6 1.5pt solid; BORDER-LEFT: #969696 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl78>0%</TD></TR></TBODY></TABLE>

 

[GlennUK]

Are you saying that you want to conditionally format dependant on those planned dates showing in that screen shot?

 

[Aimee S.]

The projects are dynamic, so yeah. Meaning if a Planned date gets moved out into the future, and a meeting is held three days later, I want that cell formatted to be bold (and/or underlined) so that the governing managers can see exactly what data has been updated/changed recently.

The percentages also require that sort of conditional formatting, meaning if % done is updated from 50% to 55% and four days later the board meets, I need them to see it in bold/underline (meaning the value changed within the past seven days).

The former issue is doable I think, I just don't know the right formula composition. The latter % issue, not so sure >.<

 

[GlennUK]

The latter issue cannot be done unless you keep a datestamped log of all changes and maintain that through all changes ... a near impossible task, I'd say.

As for highlighting whether a date cell is in the future, just compare to TODAY(), like:
=A1>TODAY()