NadoDude
Board Regular
- Joined
- Jun 24, 2010
- Messages
- 84
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>
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>