NadoDude
Board Regular
- Joined
- Jun 24, 2010
- Messages
- 84
In my worksheet below I have a formula in Column E which is calculating the number of days used to date in the Current Month based on some criteria.
In Column F, I need a formula which has the same criteria as in Column E, but want it to tell me how many days have been used to date in the current quarter.
<font size="2">Worksheet: Sheet1 UsedRange: $A$1:$N$95 Range: $A$1:$G$6</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">A</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">B</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">C</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">D</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">E</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">F</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">G</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="cA1_705"></a><font size="2">Planned Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB1_705"></a><font size="2">Actual Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC1_705"></a><font size="2">Planned End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD1_705"></a><font size="2">Actual End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE1_705"></a><font size="2">This Month to date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF1_705"></a><font size="2">This Quarter to Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG1_705"></a><font size="2">Notes</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="cA2_705"></a><font size="2">7/22/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB2_705"></a><font size="2">7/22/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC2_705"></a><font size="2">9/30/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cE2_705"></a><font size="2"><a title="=IF(B2="",0,MAX(0,MIN(IF($D2="",$C2,$D2),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B2="",$A2,$B2),0)))" href="#fE2_705">12</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG2_705"></a><font size="2">Should = 53</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="cA3_705"></a><font size="2">6/15/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB3_705"></a><font size="2">7/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC3_705"></a><font size="2">8/10/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD3_705"></a><font size="2">8/10/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE3_705"></a><font size="2"><a title="=IF(B3="",0,MAX(0,MIN(IF($D3="",$C3,$D3),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B3="",$A3,$B3),0)))" href="#fE3_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG3_705"></a><font size="2">Should = 40</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="cA4_705"></a><font size="2">5/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB4_705"></a><font size="2">5/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC4_705"></a><font size="2">10/15/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cE4_705"></a><font size="2"><a title="=IF(B4="",0,MAX(0,MIN(IF($D4="",$C4,$D4),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B4="",$A4,$B4),0)))" href="#fE4_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG4_705"></a><font size="2">Should = 92 (92 days in current Quarter)</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="cA5_705"></a><font size="2">4/2/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB5_705"></a><font size="2">4/2/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC5_705"></a><font size="2">6/23/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD5_705"></a><font size="2">6/23/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE5_705"></a><font size="2"><a title="=IF(B5="",0,MAX(0,MIN(IF($D5="",$C5,$D5),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B5="",$A5,$B5),0)))" href="#fE5_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG5_705"></a><font size="2">Should = 0 (No days used in current quarter)</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="cA6_705"></a><font size="2">9/18/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cC6_705"></a><font size="2">10/14/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cE6_705"></a><font size="2"><a title="=IF(B6="",0,MAX(0,MIN(IF($D6="",$C6,$D6),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B6="",$A6,$B6),0)))" href="#fE6_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG6_705"></a><font size="2">Should = 0 (Event has not started)</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="fE2_705"></a><font size="2"><a href="#cE2_705">E2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">12</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(B2="",0,MAX(0,MIN(IF($D2="",$C2,$D2),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B2="",$A2,$B2),0)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fE3_705"></a><font size="2"><a href="#cE3_705">E3</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(B3="",0,MAX(0,MIN(IF($D3="",$C3,$D3),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B3="",$A3,$B3),0)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fE4_705"></a><font size="2"><a href="#cE4_705">E4</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(B4="",0,MAX(0,MIN(IF($D4="",$C4,$D4),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B4="",$A4,$B4),0)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fE5_705"></a><font size="2"><a href="#cE5_705">E5</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(B5="",0,MAX(0,MIN(IF($D5="",$C5,$D5),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B5="",$A5,$B5),0)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fE6_705"></a><font size="2"><a href="#cE6_705">E6</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(B6="",0,MAX(0,MIN(IF($D6="",$C6,$D6),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B6="",$A6,$B6),0)))</font></td></tr></table></div>
In Column F, I need a formula which has the same criteria as in Column E, but want it to tell me how many days have been used to date in the current quarter.
<font size="2">Worksheet: Sheet1 UsedRange: $A$1:$N$95 Range: $A$1:$G$6</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">A</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">B</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">C</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">D</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">E</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">F</font></td><td width="100px" align="center" valign="top" style="background-color:#eeeeee;"><font size="2">G</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="cA1_705"></a><font size="2">Planned Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB1_705"></a><font size="2">Actual Start Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC1_705"></a><font size="2">Planned End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD1_705"></a><font size="2">Actual End Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE1_705"></a><font size="2">This Month to date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cF1_705"></a><font size="2">This Quarter to Date</font></td><td width="100px" style="background-color:#ffffff;"><a name="cG1_705"></a><font size="2">Notes</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="cA2_705"></a><font size="2">7/22/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB2_705"></a><font size="2">7/22/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC2_705"></a><font size="2">9/30/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cE2_705"></a><font size="2"><a title="=IF(B2="",0,MAX(0,MIN(IF($D2="",$C2,$D2),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B2="",$A2,$B2),0)))" href="#fE2_705">12</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF2_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG2_705"></a><font size="2">Should = 53</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="cA3_705"></a><font size="2">6/15/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB3_705"></a><font size="2">7/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC3_705"></a><font size="2">8/10/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD3_705"></a><font size="2">8/10/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE3_705"></a><font size="2"><a title="=IF(B3="",0,MAX(0,MIN(IF($D3="",$C3,$D3),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B3="",$A3,$B3),0)))" href="#fE3_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF3_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG3_705"></a><font size="2">Should = 40</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="cA4_705"></a><font size="2">5/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB4_705"></a><font size="2">5/1/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC4_705"></a><font size="2">10/15/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cE4_705"></a><font size="2"><a title="=IF(B4="",0,MAX(0,MIN(IF($D4="",$C4,$D4),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B4="",$A4,$B4),0)))" href="#fE4_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF4_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG4_705"></a><font size="2">Should = 92 (92 days in current Quarter)</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="cA5_705"></a><font size="2">4/2/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB5_705"></a><font size="2">4/2/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cC5_705"></a><font size="2">6/23/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD5_705"></a><font size="2">6/23/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cE5_705"></a><font size="2"><a title="=IF(B5="",0,MAX(0,MIN(IF($D5="",$C5,$D5),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B5="",$A5,$B5),0)))" href="#fE5_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF5_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG5_705"></a><font size="2">Should = 0 (No days used in current quarter)</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="cA6_705"></a><font size="2">9/18/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cB6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cC6_705"></a><font size="2">10/14/2010</font></td><td width="100px" style="background-color:#ffffff;"><a name="cD6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cE6_705"></a><font size="2"><a title="=IF(B6="",0,MAX(0,MIN(IF($D6="",$C6,$D6),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B6="",$A6,$B6),0)))" href="#fE6_705">0</a></font></td><td width="100px" style="background-color:#ffffff;"><a name="cF6_705"></a><font size="2"></font></td><td width="100px" style="background-color:#ffffff;"><a name="cG6_705"></a><font size="2">Should = 0 (Event has not started)</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="fE2_705"></a><font size="2"><a href="#cE2_705">E2</a></font></td><td width="100px" style="background-color:#ffffff;"><font size="2">12</font></td><td width="100px" style="background-color:#ffffff;"><font size="2">=IF(B2="",0,MAX(0,MIN(IF($D2="",$C2,$D2),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B2="",$A2,$B2),0)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fE3_705"></a><font size="2"><a href="#cE3_705">E3</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(B3="",0,MAX(0,MIN(IF($D3="",$C3,$D3),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B3="",$A3,$B3),0)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fE4_705"></a><font size="2"><a href="#cE4_705">E4</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(B4="",0,MAX(0,MIN(IF($D4="",$C4,$D4),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B4="",$A4,$B4),0)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fE5_705"></a><font size="2"><a href="#cE5_705">E5</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(B5="",0,MAX(0,MIN(IF($D5="",$C5,$D5),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B5="",$A5,$B5),0)))</font></td></tr><tr><td width="40px" style="background-color:#eeeeee;"><a name="fE6_705"></a><font size="2"><a href="#cE6_705">E6</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(B6="",0,MAX(0,MIN(IF($D6="",$C6,$D6),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF($B6="",$A6,$B6),0)))</font></td></tr></table></div>