Help with formula

[NadoDude]

I am using the formula in Column Q to total the number of days a person was gone in the last 730 days. The formula works correctly if the Actual End Date (Column P) is filled in.

I need to add something to the formula that says, if P is blank, use todays date, if not blank use the date in P.

If working correctly the value in Q2 should be 196. (TODAY()-N2 = 196)

<font size="2">Worksheet: ITEMPO Events UsedRange: $A$1:$CI$7799 Range: $N$1:$Q$3</font><br /><br /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2"> </font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">N</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">O</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">P</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Q</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN1_705"></a><font size="2">Actual Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO1_705"></a><font size="2">Planned End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP1_705"></a><font size="2">Actual End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ1_705"></a><font size="2">Days Used Last 730</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">2</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN2_705"></a><font size="2">2/25/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO2_705"></a><font size="2">9/20/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ2_705"></a><font size="2"><a title="=IF($P2<(TODAY()-730),0,IF($N2<(TODAY()-730),$P2-(TODAY()-730),$P2-$N2))" href="#fQ2_705">0</a></font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN3_705"></a><font size="2">3/25/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO3_705"></a><font size="2">9/20/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP3_705"></a><font size="2">7/8/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ3_705"></a><font size="2"><a title="=IF($P3<(TODAY()-730),0,IF($N3<(TODAY()-730),$P3-(TODAY()-730),$P3-$N3))" href="#fQ3_705">105</a></font></td></table></div><p /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Address</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Value</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Formula</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ2_705"></a><font size="2"><a href="#cQ2_705">Q2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($P2<(TODAY()-730),0,IF($N2<(TODAY()-730),$P2-(TODAY()-730),$P2-$N2))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ3_705"></a><font size="2"><a href="#cQ3_705">Q3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">105</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($P3<(TODAY()-730),0,IF($N3<(TODAY()-730),$P3-(TODAY()-730),$P3-$N3))</font></td></tr></table></div>

 

[lenze]

My guess!!!

=MIN(730,IF(P2,P2-N2,TODAY()-N2))

lenze

 

[NadoDude]

Are you saying replace my formula with the one you provided?

 

[lenze]

I wasn't really sure what you wanted exactly, but it's worth a shot!! Post back your results if you don't get the answer you expected!!
lenze

 

[NadoDude]

Sorry my original post was confusing. It is often hard for me to write what I am thinking clearly.

Below is a snapshot of the data which I hope will show what I am seeing and trying to accomplish.

I added an IF statement to your formula to not calculate future events.


<font size="2">Worksheet: Sheet1 UsedRange: $M$1:$S$7 Range: $M$1:$S$7</font><br /><br /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2"> </font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">M</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">N</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">O</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">P</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Q</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">R</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">S</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">1</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM1_705"></a><font size="2">Planned Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN1_705"></a><font size="2">Actual Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO1_705"></a><font size="2">Planned End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP1_705"></a><font size="2">Actual End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ1_705"></a><font size="2">NadoDude</font></td><td width="100px" style="background-color:#ffffff;"><a name="cR1_705"></a><font size="2">Lenze</font></td><td width="100px" style="background-color:#ffffff;"><a name="cS1_705"></a><font size="2">Comment</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">2</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM2_705"></a><font size="2">8/28/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN2_705"></a><font size="2">8/28/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO2_705"></a><font size="2">9/7/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP2_705"></a><font size="2">9/7/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ2_705"></a><font size="2"><a title="=IF($P2<(TODAY()-730),0,IF($N2<(TODAY()-730),$P2-(TODAY()-730),$P2-$N2))" href="#fQ2_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR2_705"></a><font size="2"><a title="=IF($N2="",0,MIN(365,IF($P2,$P2-$N2,TODAY()-$N2)))" href="#fR2_705">10</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS2_705"></a><font size="2">R2 should equal 0 since P is less than 9/9/2008</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM3_705"></a><font size="2">6/9/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN3_705"></a><font size="2">6/9/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO3_705"></a><font size="2">11/8/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP3_705"></a><font size="2">11/3/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ3_705"></a><font size="2"><a title="=IF($P3<(TODAY()-730),0,IF($N3<(TODAY()-730),$P3-(TODAY()-730),$P3-$N3))" href="#fQ3_705">55</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR3_705"></a><font size="2"><a title="=IF($N3="",0,MIN(365,IF($P3,$P3-$N3,TODAY()-$N3)))" href="#fR3_705">147</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS3_705"></a><font size="2">R2 should equal 55 since N is less than 9/9/2008, it is using 11/3/2008-9/9/2008</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">4</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM4_705"></a><font size="2">9/6/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN4_705"></a><font size="2">9/6/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO4_705"></a><font size="2">9/12/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP4_705"></a><font size="2">9/12/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ4_705"></a><font size="2"><a title="=IF($P4<(TODAY()-730),0,IF($N4<(TODAY()-730),$P4-(TODAY()-730),$P4-$N4))" href="#fQ4_705">3</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR4_705"></a><font size="2"><a title="=IF($N4="",0,MIN(365,IF($P4,$P4-$N4,TODAY()-$N4)))" href="#fR4_705">6</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS4_705"></a><font size="2">R2 should equal 55 since N is less than 9/9/2008, it is using 9/12/08-9/9/2008</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">5</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM5_705"></a><font size="2">9/9/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN5_705"></a><font size="2">9/9/2008</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO5_705"></a><font size="2">4/30/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP5_705"></a><font size="2">3/5/2009</font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ5_705"></a><font size="2"><a title="=IF($P5<(TODAY()-730),0,IF($N5<(TODAY()-730),$P5-(TODAY()-730),$P5-$N5))" href="#fQ5_705">177</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR5_705"></a><font size="2"><a title="=IF($N5="",0,MIN(365,IF($P5,$P5-$N5,TODAY()-$N5)))" href="#fR5_705">177</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS5_705"></a><font size="2">both calculate correctly</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">6</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM6_705"></a><font size="2">2/25/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN6_705"></a><font size="2">2/25/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO6_705"></a><font size="2">9/20/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ6_705"></a><font size="2"><a title="=IF($P6<(TODAY()-730),0,IF($N6<(TODAY()-730),$P6-(TODAY()-730),$P6-$N6))" href="#fQ6_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR6_705"></a><font size="2"><a title="=IF($N6="",0,MIN(365,IF($P6,$P6-$N6,TODAY()-$N6)))" href="#fR6_705">196</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS6_705"></a><font size="2">Q5 should equal 196</font></td><tr><td width="40px" style="background-color:#eeeeee;"><font size="2">7</font></td><td width="100px" style="background-color:#ffffff;"><a name="cM7_705"></a><font size="2">9/8/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cO7_705"></a><font size="2">9/15/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cP7_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cQ7_705"></a><font size="2"><a title="=IF($P7<(TODAY()-730),0,IF($N7<(TODAY()-730),$P7-(TODAY()-730),$P7-$N7))" href="#fQ7_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR7_705"></a><font size="2"><a title="=IF($N7="",0,MIN(365,IF($P7,$P7-$N7,TODAY()-$N7)))" href="#fR7_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cS7_705"></a><font size="2">both calculate correctly</font></td></table></div><p /><div style="border: 1px solid #666666; overflow: auto; width:580px; height: 220px;"><table border="1" cellspacing="2" cellpadding="3"><tr><td width="40px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Address</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Value</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">Formula</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ2_705"></a><font size="2"><a href="#cQ2_705">Q2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($P2<(TODAY()-730),0,IF($N2<(TODAY()-730),$P2-(TODAY()-730),$P2-$N2))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fR2_705"></a><font size="2"><a href="#cR2_705">R2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">10</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($N2="",0,MIN(365,IF($P2,$P2-$N2,TODAY()-$N2)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ3_705"></a><font size="2"><a href="#cQ3_705">Q3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">55</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($P3<(TODAY()-730),0,IF($N3<(TODAY()-730),$P3-(TODAY()-730),$P3-$N3))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fR3_705"></a><font size="2"><a href="#cR3_705">R3</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">147</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($N3="",0,MIN(365,IF($P3,$P3-$N3,TODAY()-$N3)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ4_705"></a><font size="2"><a href="#cQ4_705">Q4</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">3</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($P4<(TODAY()-730),0,IF($N4<(TODAY()-730),$P4-(TODAY()-730),$P4-$N4))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fR4_705"></a><font size="2"><a href="#cR4_705">R4</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">6</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($N4="",0,MIN(365,IF($P4,$P4-$N4,TODAY()-$N4)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ5_705"></a><font size="2"><a href="#cQ5_705">Q5</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">177</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($P5<(TODAY()-730),0,IF($N5<(TODAY()-730),$P5-(TODAY()-730),$P5-$N5))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fR5_705"></a><font size="2"><a href="#cR5_705">R5</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">177</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($N5="",0,MIN(365,IF($P5,$P5-$N5,TODAY()-$N5)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ6_705"></a><font size="2"><a href="#cQ6_705">Q6</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($P6<(TODAY()-730),0,IF($N6<(TODAY()-730),$P6-(TODAY()-730),$P6-$N6))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fR6_705"></a><font size="2"><a href="#cR6_705">R6</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">196</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($N6="",0,MIN(365,IF($P6,$P6-$N6,TODAY()-$N6)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fQ7_705"></a><font size="2"><a href="#cQ7_705">Q7</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($P7<(TODAY()-730),0,IF($N7<(TODAY()-730),$P7-(TODAY()-730),$P7-$N7))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fR7_705"></a><font size="2"><a href="#cR7_705">R7</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">0</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($N7="",0,MIN(365,IF($P7,$P7-$N7,TODAY()-$N7)))</font></td></tr></table></div>

 

[lenze]

I'm confused!! Where are you getting 9/9/2008? Do you mean 9/92010??

lenze

 

[NadoDude]

9/9/2008 is TODAY()-730