I can’t get our inventories to project out correctly...I ran into a glitch on a 30 day limiter someone passed my way. It seems to trigger the conditional formatting regardless if the date has passed (i.e. it would show "green" for May 25 or May 15 because both dates are within the 30 days of today and I don't need info from the past).
Can you help me create a different rule that once that date passes (or an inventory cell has been loaded with a date) then the conditional format will not appear? I have ours set up for 30 days out (red), 60 days out(yellow), and 90 days out (green) for when we need to conduct the upcoming or current inventory.
I toyed around with the if/then feature but it too has a window for days rather than once data is put in to reject the conditional format (see below)
Green:
=IF(C2:C97=0,THEN,(D2:D96+365)-TODAY())<=90
Yellow:
=IF(C2:C97=0,THEN,(D2:D96+365)-TODAY())<=60
Green:
=IF(C2:C97=0,THEN,(D2:D96+365)-TODAY())<=30
What I am trying to do is highlight/project our upcoming, current, and non-existent inventories. Using today's date you can see what I am trying to do.
1) Highlight any item that is upcoming within the parameters
2) Know not to include the 3rd column if there is a date in the 4th
3) Highlight the entire row if there is no record of an inventory
These aren't conditionally formatted (just colored), but this is what I want to make our 96 row spreadsheet look like. Can you/anyone help?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
<TABLE style="WIDTH: 349pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=465><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; BACKGROUND: silver; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=64>Item<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; WIDTH: 203pt; PADDING-RIGHT: 0in; BACKGROUND: silver; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=271>Project Description<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; BACKGROUND: silver; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=64>2011 Inventory<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; WIDTH: 50pt; PADDING-RIGHT: 0in; BACKGROUND: silver; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=67>2012 Inventory<o
></o
>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Project A<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Alpha project<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Project B<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Bravo project<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Project C<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Charlie project<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #00b050; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Project D<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Delta project<o
></o
>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD></TR></TBODY></TABLE>
The wishful thinking side of me would love to be able to figure out how the conditional formatting would trigger on the first day of the month (i.e., an inventory is due Jun 15, so on May 1, the yellow conditional format would turn red rather than wait until May 15...to be 30 days out) <o
></o
>
<o
> </o
>
Can you help me create a different rule that once that date passes (or an inventory cell has been loaded with a date) then the conditional format will not appear? I have ours set up for 30 days out (red), 60 days out(yellow), and 90 days out (green) for when we need to conduct the upcoming or current inventory.
I toyed around with the if/then feature but it too has a window for days rather than once data is put in to reject the conditional format (see below)
Green:
=IF(C2:C97=0,THEN,(D2:D96+365)-TODAY())<=90
Yellow:
=IF(C2:C97=0,THEN,(D2:D96+365)-TODAY())<=60
Green:
=IF(C2:C97=0,THEN,(D2:D96+365)-TODAY())<=30
What I am trying to do is highlight/project our upcoming, current, and non-existent inventories. Using today's date you can see what I am trying to do.
1) Highlight any item that is upcoming within the parameters
2) Know not to include the 3rd column if there is a date in the 4th
3) Highlight the entire row if there is no record of an inventory
These aren't conditionally formatted (just colored), but this is what I want to make our 96 row spreadsheet look like. Can you/anyone help?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com




<TABLE style="WIDTH: 349pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=465><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; BACKGROUND: silver; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=64>Item<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; WIDTH: 203pt; PADDING-RIGHT: 0in; BACKGROUND: silver; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=271>Project Description<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; BACKGROUND: silver; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=64>2011 Inventory<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; WIDTH: 50pt; PADDING-RIGHT: 0in; BACKGROUND: silver; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in" width=67>2012 Inventory<o


</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Project A<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Alpha project<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">
1-Jun-11<o
></o
>




</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Bravo project<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: red; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in"></TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Project C<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Charlie project<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">
1-Apr-11<o
></o
>


15-Apr-12<o
></o
>




</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">Delta project<o


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; BACKGROUND: #f2f2f2; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in">
1-Aug-11<o
></o
>


The wishful thinking side of me would love to be able to figure out how the conditional formatting would trigger on the first day of the month (i.e., an inventory is due Jun 15, so on May 1, the yellow conditional format would turn red rather than wait until May 15...to be 30 days out) <o


<o

