Counting down days

[jdc0430]

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-comfficeffice" /><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">

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: #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">

1-Apr-11<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">

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

</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">

1-Aug-11<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></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>

 

[Joe4]

So it sounds to me like you want it to be less than 30 days, but greater than 0 (because if it is negative, then the date has passed).

So change your structure from:

=formula <= 30

to

=AND(formula <= 30,formula > 0)

The second checks to see if your value is between 0 and 30.
You could apply the same logic to see if it is between 30 and 60, etc.

 

[jdc0430]

So it sounds to me like you want it to be less than 30 days, but greater than 0 (because if it is negative, then the date has passed).

So change your structure from:

=formula <= 30

to

=AND(formula <= 30,formula > 0)

The second checks to see if your value is between 0 and 30.
You could apply the same logic to see if it is between 30 and 60, etc.

Unfortunately, I have a few formulas within that position. Can you be more specific, i.e. example the whole line for one of them?

Thank you for the patience,
Jonathan

 

[Joe4]

Unfortunately, I have a few formulas within that position.

I am talking about your Conditional Formatting formulas.
If you need help to write those formulas, you will need to tell us what values are in what cells (your screen print does not contain cell or row references).

 

[jdc0430]

I am talking about your Conditional Formatting formulas.
If you need help to write those formulas, you will need to tell us what values are in what cells (your screen print does not contain cell or row references).

Thanks for the quick reply! I understood the conditional format; what I was talking about was how my formula has an "if" and two parenthetical formulas prior to the < 30/60/90. There are no more formulas withing the page other than those three.

What I was looking for is your assistance in rewriting 1 of the lines using your advice with the formulas I supplied, say the 30 day, that I can apply to the 60 & 90 without wasting too much of your time.

 

[Joe4]

Which is column C and which is column D?
In your IF statement, you are checking column C to see if it is zero.

I found your initial description a little confusing. So let's start over with just the 30 day, like you said.

Can you simply describe how the Conditional Formatting should work (in plan English) making certain to tell what has to happen in columns C and D for it to be True?

 

[jdc0430]

Which is column C and which is column D?
In your IF statement, you are checking column C to see if it is zero.

I found your initial description a little confusing. So let's start over with just the 30 day, like you said.

Can you simply describe how the Conditional Formatting should work (in plan English) making certain to tell what has to happen in columns C and D for it to be True?

Thanks for noticing that. They are as they appear...column A=item, B=description; C=2011 inventory; D=2012 inventory.

I am trying to create a rule that highlights if an inventory has been done...hence the "if" scenario. If there hasn't been one in the last 11 months (or is due w/in30 days), I want the "item" cell highlighted red. If it hasn't been done in 10 months (or due w/in 2), that item cell should be yellow. If it hasn't been done in 9 months (or is due in 3 months/90 days) that cell should be green.

 

[Joe4]

I guess what I am finding a bit confusing is you have two date columns, 2011 and 2012. So when we are checking dates, is this an "either or" or an "and" situation? That is, are we checking both dates, or just one? Is the logic the same for both fields?

 

[jdc0430]

I guess what I am finding a bit confusing is you have two date columns, 2011 and 2012. So when we are checking dates, is this an "either or" or an "and" situation? That is, are we checking both dates, or just one? Is the logic the same for both fields?

I SEE...says the blind man! Essentially,this spreadsheet will be recycled every Jan 1. The 2012 (column D) is for updating after that particular inventory ha been complete. Nothing more is needed in that column. It is so we know that it had been done sober can transfer that date to the next calendar year spreadsheet and repeat all over again. All formulas should populate based on 2011 dates (column C) into Column A (Item) by color coding it.

Thanks!

 

[Joe4]

OK, let's look at the following example and see if it answers your question, or at least sends you down the right path.

Let's say that you want to highlight the cell A2 if the date in C2 is in the past, within 30 days (0-30 days less than the current date). Then the Conditional Formatting formula you place on cell A2 would look something:

=and((today()-$c2)>0,(today()-$c2)<30)

Does that help?