# Thread: Calculate number of days used in current quarter Thanks: 0 Likes: 0

1. ## Calculate number of days used in current quarter

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.

Worksheet: Sheet1 UsedRange: \$A\$1:\$N\$95 Range: \$A\$1:\$G\$6

 A B C D E F G 1 Planned Start Date Actual Start Date Planned End Date Actual End Date This Month to date This Quarter to Date Notes 2 7/22/2010 7/22/2010 9/30/2010 12 Should = 53 3 6/15/2010 7/1/2010 8/10/2010 8/10/2010 0 Should = 40 4 5/1/2010 5/1/2010 10/15/2010 0 Should = 92 (92 days in current Quarter) 5 4/2/2010 4/2/2010 6/23/2010 6/23/2010 0 Should = 0 (No days used in current quarter) 6 9/18/2010 10/14/2010 0 Should = 0 (Event has not started)

 Address Value Formula E2 12 =IF(B2="",0,MAX(0,MIN(IF(\$D2="",\$C2,\$D2),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF(\$B2="",\$A2,\$B2),0))) E3 0 =IF(B3="",0,MAX(0,MIN(IF(\$D3="",\$C3,\$D3),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF(\$B3="",\$A3,\$B3),0))) E4 0 =IF(B4="",0,MAX(0,MIN(IF(\$D4="",\$C4,\$D4),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF(\$B4="",\$A4,\$B4),0))) E5 0 =IF(B5="",0,MAX(0,MIN(IF(\$D5="",\$C5,\$D5),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF(\$B5="",\$A5,\$B5),0))) E6 0 =IF(B6="",0,MAX(0,MIN(IF(\$D6="",\$C6,\$D6),TODAY())-MAX(DATE(YEAR(TODAY()),MONTH(TODAY()),1),IF(\$B6="",\$A6,\$B6),0)))

2. ## Re: Calculate number of days used in current quarter

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)))

3. ## Re: Calculate number of days used in current quarter

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•