NadoDude
Board Regular
- Joined
- Jun 24, 2010
- Messages
- 84
I am using the following formula to calculate the number of days a person has used in the current quarter.
=IF($N2="",0,MAX(0,MIN(IF($P2="",$O2,$P2),TODAY())-MAX(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1),IF($N2="",$M2,$N2),0)))
Is there any way I can modify the formula to tell me home many days the person used last quarter?
Column M is Planned Start Date
Column N is Actual Start Date
Column O is Planned End Date
Column P is Actual End Date
<font size="2">Worksheet: Sheet1 UsedRange: $M$1:$R$2 Range: $M$1:$R$2</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></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">This Quarter to Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cR1_705"></a><font size="2">Last Quarter</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">5/5/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN2_705"></a><font size="2">5/5/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO2_705"></a><font size="2">11/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($N2="",0,MAX(0,MIN(IF($P2="",$O2,$P2),TODAY())-MAX(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1),IF($N2="",$M2,$N2),0)))" href="#fQ2_705">76</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR2_705"></a><font size="2"></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">76</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($N2="",0,MAX(0,MIN(IF($P2="",$O2,$P2),TODAY())-MAX(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1),IF($N2="",$M2,$N2),0)))</font></td></tr></table></div>
=IF($N2="",0,MAX(0,MIN(IF($P2="",$O2,$P2),TODAY())-MAX(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1),IF($N2="",$M2,$N2),0)))
Is there any way I can modify the formula to tell me home many days the person used last quarter?
Column M is Planned Start Date
Column N is Actual Start Date
Column O is Planned End Date
Column P is Actual End Date
<font size="2">Worksheet: Sheet1 UsedRange: $M$1:$R$2 Range: $M$1:$R$2</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></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">This Quarter to Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cR1_705"></a><font size="2">Last Quarter</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">5/5/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cN2_705"></a><font size="2">5/5/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cO2_705"></a><font size="2">11/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($N2="",0,MAX(0,MIN(IF($P2="",$O2,$P2),TODAY())-MAX(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1),IF($N2="",$M2,$N2),0)))" href="#fQ2_705">76</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cR2_705"></a><font size="2"></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">76</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF($N2="",0,MAX(0,MIN(IF($P2="",$O2,$P2),TODAY())-MAX(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1),IF($N2="",$M2,$N2),0)))</font></td></tr></table></div>