Hi Guys,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am after some guidance regarding trying to set Conditional to check the dates on my spreadsheet and return back as RED if that date has passed<o></o>
Now the issue I have is I have used Vlookup’s and other formulas to calculate the dates so when I add the formula for Conditional Formatting it doesn’t recognise the date due to the formula in the cell... see below<o></o>
.....What I want is If 29/04/2010 is less than today date then format the cell<o></o>
This is the Imbedded formula..<o></o>
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 425pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=567><TBODY><TR style="HEIGHT: 16.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 141pt; PADDING-RIGHT: 5.4pt; HEIGHT: 16.5pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; PADDING-TOP: 0cm" width=188>=VLOOKUP(B5,$IQ$4:$IR$1298,2) (Day of the Week)<o></o>
</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 141pt; PADDING-RIGHT: 5.4pt; HEIGHT: 16.5pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; PADDING-TOP: 0cm" width=188>=B6-1 (The date I want to format)<o></o>
</TD></TR></TBODY></TABLE>
Is there a way of changing the formula in these date cells so it will recognise the data in the cell and not the formula?<o></o>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="Z-INDEX: -2; POSITION: absolute; MARGIN-TOP: 110.25pt; WIDTH: 252pt; HEIGHT: 238.5pt; VISIBILITY: visible; MARGIN-LEFT: 114pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" id=Picture_x0020_1 type="#_x0000_t75" o:spid="_x0000_s1027"><v:imagedata src="file:///C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.png" cropright="32816f" cropbottom="3756f" croptop="20593f" o:title=""></v:imagedata></v:shape><v:shape style="Z-INDEX: -1; POSITION: absolute; MARGIN-TOP: 116.25pt; WIDTH: 135.75pt; HEIGHT: 222.75pt; VISIBILITY: visible; MARGIN-LEFT: -51.75pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" id=Picture_x0020_4 type="#_x0000_t75" o:spid="_x0000_s1026"><v:imagedata src="file:///C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image003.png" cropright="47219f" cropbottom="3911f" croptop="21576f" o:title=""></v:imagedata></v:shape>Thanks again<o></o>
Steve
I am after some guidance regarding trying to set Conditional to check the dates on my spreadsheet and return back as RED if that date has passed<o></o>
Now the issue I have is I have used Vlookup’s and other formulas to calculate the dates so when I add the formula for Conditional Formatting it doesn’t recognise the date due to the formula in the cell... see below<o></o>
.....What I want is If 29/04/2010 is less than today date then format the cell<o></o>
This is the Imbedded formula..<o></o>
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 425pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=567><TBODY><TR style="HEIGHT: 16.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 141pt; PADDING-RIGHT: 5.4pt; HEIGHT: 16.5pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; PADDING-TOP: 0cm" width=188>=VLOOKUP(B5,$IQ$4:$IR$1298,2) (Day of the Week)<o></o>
</TD><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 141pt; PADDING-RIGHT: 5.4pt; HEIGHT: 16.5pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed; PADDING-TOP: 0cm" width=188>=B6-1 (The date I want to format)<o></o>
</TD></TR></TBODY></TABLE>
Is there a way of changing the formula in these date cells so it will recognise the data in the cell and not the formula?<o></o>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="Z-INDEX: -2; POSITION: absolute; MARGIN-TOP: 110.25pt; WIDTH: 252pt; HEIGHT: 238.5pt; VISIBILITY: visible; MARGIN-LEFT: 114pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" id=Picture_x0020_1 type="#_x0000_t75" o:spid="_x0000_s1027"><v:imagedata src="file:///C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.png" cropright="32816f" cropbottom="3756f" croptop="20593f" o:title=""></v:imagedata></v:shape><v:shape style="Z-INDEX: -1; POSITION: absolute; MARGIN-TOP: 116.25pt; WIDTH: 135.75pt; HEIGHT: 222.75pt; VISIBILITY: visible; MARGIN-LEFT: -51.75pt; mso-wrap-style: square; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text" id=Picture_x0020_4 type="#_x0000_t75" o:spid="_x0000_s1026"><v:imagedata src="file:///C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\msohtmlclip1\01\clip_image003.png" cropright="47219f" cropbottom="3911f" croptop="21576f" o:title=""></v:imagedata></v:shape>Thanks again<o></o>
Steve