# Calculate number of days used in current quarter

##### Board Regular
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>

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### barry houdini

##### MrExcel MVP
I'm not sure how row 4 would be 92 as we aren't yet at the end of the current quarter. If you use this version in row 2 copied down you get all the results you suggested...except row 4 is 74

=IF(B2="",0,MAX(0,MIN(IF(\$D2="",\$C2,\$D2),TODAY())-MAX(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-1,3)+1,1),IF(\$B2="",\$A2,\$B2),0)))

##### Board Regular
You are correct - Row 4 should be 74. Thanks for keeping me honest. You solution appears to works great. Thanks for the assistance.

1,102,274
Messages
5,485,782
Members
407,515
Latest member
franjey

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...