Formulas with altenating cells?

[yani06]

My basic formula is this: =INT((E3-D3)/7)

but if E3>$G$9 then E3 should = $G$9

and if D3<$G$10 then D3 should = $G$10

and if E3 is blank then it should also = $G$9

Is there a way to merge all these and create one formula that I can drag across the column?

 

[yani06]

I separated the formula and created new columns, to see where I was having trouble. The trouble is in the second part of the formula.
IF(D3<$C$16,$C$16,D3))/7)
It's not changing D3 date to $C$16 if D3 is before $C$16.
I think the formula is missing an OR part.

 

[jasonb75]

It should work as long as your ranges are correct and your dates are vaild.

Try my alternative from #17

=MAX(INT((IF(OR(E3>$G$17,E3=""),$G$17,E3)-MAX($G$16,D3))/7),1)

Although it should give exactly the same results as mark's formula.

 

[yani06]

I might not have explained myself well enough, because it's not working. Let me get back to trying to understand the formula and see if I can explain better.

 

[jasonb75]

What do you have in each of the cells, E3, G17, G16 and D3?

What result do you expect from that?

 

[yani06]

D3 is a starting date. E3 is an end date.

If D3 is before the G16 date, then it should use the G16 date. If D3 equals or is later than G16 then it stays D3.

If E3 is blank then it changes to G17. If E3 has text then it stays E3.

Then I use the formula to calculate how many weeks. They cannot be more than 208 per the dates given and they have to be at least 1.

I have been playing with the formulas and columns, so I think I may have confused it all more since my original question. Sorry and again thanks for any input.

 

[markmzz]

Look at the cells in yellow:

I think that your's formulas isn't ok. The values in column D are before the value in the cell C16 (D9 and D10 are < C16).

Excel 2007 <TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">new formula</TD><TD style="TEXT-ALIGN: center">your formula</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">10/26/2007</TD><TD style="TEXT-ALIGN: center">8/12/2008</TD><TD style="TEXT-ALIGN: center">41</TD><TD style="TEXT-ALIGN: center">41</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">5/4/2009</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">92</TD><TD style="TEXT-ALIGN: center">92</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">2/8/1992</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">208</TD><TD style="TEXT-ALIGN: center">208</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">2/25/2010</TD><TD style="TEXT-ALIGN: center">3/4/2010</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">10/19/2009</TD><TD style="TEXT-ALIGN: center">5/7/2010</TD><TD style="TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: center">28</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">10/10/2010</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">17</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">11/27/2006</TD><TD style="TEXT-ALIGN: center">8/21/2009</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">132</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">142</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">10/9/2006</TD><TD style="TEXT-ALIGN: center">5/2/2008</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">64</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">81</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">6/23/2009</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">84</TD><TD style="TEXT-ALIGN: center">84</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right">2/7/2007</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right">2/7/2011</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Sheet3


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas <TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F3</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E3>$C$17,E3=""),$C$17,E3)-IF(D3<$C$16,$C$16,D3))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G3</TH><TD style="TEXT-ALIGN: left">=INT((E3-D3)/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F4</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E4>$C$17,E4=""),$C$17,E4)-IF(D4<$C$16,$C$16,D4))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G4</TH><TD style="TEXT-ALIGN: left">=INT(($C$17-D4)/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F5</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E5>$C$17,E5=""),$C$17,E5)-IF(D5<$C$16,$C$16,D5))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G5</TH><TD style="TEXT-ALIGN: left">=INT(($C17-$C16)/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F6</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E6>$C$17,E6=""),$C$17,E6)-IF(D6<$C$16,$C$16,D6))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G6</TH><TD style="TEXT-ALIGN: left">=INT((E6-D6)/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F7</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E7>$C$17,E7=""),$C$17,E7)-IF(D7<$C$16,$C$16,D7))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G7</TH><TD style="TEXT-ALIGN: left">=INT((E7-D7)/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F8</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E8>$C$17,E8=""),$C$17,E8)-IF(D8<$C$16,$C$16,D8))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G8</TH><TD style="TEXT-ALIGN: left">=INT(($C$17-D8)/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F9</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E9>$C$17,E9=""),$C$17,E9)-IF(D9<$C$16,$C$16,D9))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G9</TH><TD style="TEXT-ALIGN: left">=INT((E9-D9)/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F10</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E10>$C$17,E10=""),$C$17,E10)-IF(D10<$C$16,$C$16,D10))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G10</TH><TD style="TEXT-ALIGN: left">=INT((E10-D10)/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F11</TH><TD style="TEXT-ALIGN: left">=INT((IF(OR(E11>$C$17,E11=""),$C$17,E11)-IF(D11<$C$16,$C$16,D11))/7)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G11</TH><TD style="TEXT-ALIGN: left">=INT(($C17-D11)/7)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

D3 is a starting date. E3 is an end date.

If D3 is before the G16 date, then it should use the G16 date. If D3 equals or is later than G16 then it stays D3.

If E3 is blank then it changes to G17. If E3 has text then it stays E3.

Then I use the formula to calculate how many weeks. They cannot be more than 208 per the dates given and they have to be at least 1.

I have been playing with the formulas and columns, so I think I may have confused it all more since my original question. Sorry and again thanks for any input.

Yani06,

Look at this:

The values:

D9=11/27/2006
E9=8/21/2009

C16=2/7/2007
C17=2/7/2011

With your's rules:

E9 < C17 then E9
D9 < C16 then C16<C16 font then C16<>

Your formula:

G9: =INT((E9-D9)/7) result 142

My formula:

F9: =INT((E9-C16)/7) result 132

So, I think that your rules isn't ok.

Markmzz

 

[jasonb75]

D3 is a starting date. E3 is an end date.

If D3 is before the G16 date, then it should use the G16 date. If D3 equals or is later than G16 then it stays D3.

If E3 is blank then it changes to G17. If E3 has text then it stays E3.

Then I use the formula to calculate how many weeks. They cannot be more than 208 per the dates given and they have to be at least 1.

I have been playing with the formulas and columns, so I think I may have confused it all more since my original question. Sorry and again thanks for any input.

Yani, the formula I quoted earlier, and Mark's equivilant should both comply with those rules.

The exception being when E3 holds a date, the formula as it was given to you uses the earliest of the 2 dates, if that is wrong then maybe you want

=MAX(INT((MIN($G$17,E3)-MAX($G$16,D3))/7),1)

edit:-

Which is the formula that Aladin gave to you in the first reply, the only difference being the MAX() function that surrounds it to prevent zero results.

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Result</td><td style="text-align: center;;">your formula</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">10/26/2007</td><td style="text-align: center;;">8/12/2008</td><td style="text-align: center;;">41</td><td style="text-align: center;;">41</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: center;;">5/4/2009</td><td style="text-align: center;;"></td><td style="text-align: center;;">92</td><td style="text-align: center;;">92</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">2/8/1992</td><td style="text-align: center;;"></td><td style="text-align: center;;">208</td><td style="text-align: center;;">208</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: center;;">2/25/2010</td><td style="text-align: center;;">3/4/2010</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: center;;">10/19/2009</td><td style="text-align: center;;">5/7/2010</td><td style="text-align: center;;">28</td><td style="text-align: center;;">28</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;">10/10/2010</td><td style="text-align: center;;"></td><td style="text-align: center;;">17</td><td style="text-align: center;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">11/27/2006</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">8/21/2009</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">142</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">142</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">10/9/2006</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">5/2/2008</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">81</td><td style="font-weight: bold;text-align: center;background-color: #FFFF00;;">81</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: center;;">6/23/2009</td><td style="text-align: center;;"></td><td style="text-align: center;;">84</td><td style="text-align: center;;">84</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2/7/2007</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">2/7/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">****</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet31</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=MAX(<font color="Blue">INT(<font color="Red">(<font color="Green">IF(<font color="Purple">OR(<font color="Teal">E3>$C$17,E3=""</font>),$C$17,E3</font>)-IF(<font color="Purple">AND(<font color="Teal">E3="",D3<$C$16</font>),$C$16,D3</font>)</font>)/7</font>),1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=INT(<font color="Blue">(<font color="Red">E3-D3</font>)/7</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[markmzz]

A little small formula (with part of Aladin's formula):

=MAX(INT((MIN(E3,$C$17)-IF(AND(E3="",D3<$C$16),$C$16,D3))/7),1)

Markmzz

 

[yani06]

A little small formula (with part of Aladin's formula):

=MAX(INT((MIN(E3,$C$17)-IF(AND(E3="",D3<$C$16),$C$16,D3))/7),1)

Markmzz

Would a different version of excel make a difference? I have 2003.

When I use the formula above, I get these results.

<TABLE style="WIDTH: 127pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=169 border=0 x:str><COLGROUP><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 127pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=169 height=20 x:num>171

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 id=td_post_2877564 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>92</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>991</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>28</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>17</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>219</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>226</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20 x:num>84</TD></TR></TBODY></TABLE>