Thanks:  0
Likes:  0

# Thread: Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

1. ## Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

Hi,
Seeking additional brain power on this one. I need to calculate the number of weeks between the start of a quarter and the end of a quarter. Sounds easy right? I need to count only a full week Monday through Sunday. This is where I'm getting brain locked.

I was using this formula =INT(C2-B2)/7 but it does not take into consideration a full week Monday through to Sunday.
If you look at Jan, Feb, and Mar on a calendar and count the full weeks, Jan has 4, Feb has 3, Mar has 3 so the total count would be 10 complete weeks Mon-Sun week.

Here are my quarter start and end dates

start end
1/1/2017 3/31/2017
4/1/2017 6/30/2017
7/1/2017 9/30/2017
10/1/2017 12/31/2017

2. ## Re: Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

Code:
```Excel 2007 32 bit
Start
End

1/1/2017
3/31/2017
13

=(B2-A2)/7

4/1/2017
6/30/2017
13

7/1/2017
9/30/2017
13

10/1/2017
12/31/2017
13

```
Paste =B2-A2)/7 in C2 and copy down.

3. ## Re: Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

Here are some formulas that may help.

In D2:
Code:
`=IF(WEEKDAY(B2,2)=1,B2,B2-WEEKDAY(B2,2)+8)`
Will display the next Monday following the start date, if it's not already a Monday.

In E2:
Code:
`=IF(WEEKDAY(C2,2)=7,C2,C2-WEEKDAY(C2,2)+0)`
Will display the previous Sunday from the end date, provided it's not already a Sunday

In F2:
Code:
`=(E2-D2+1)/7`
Will give the number of full weeks, Monday through Sunday, with no fractions.

Or you could put it all together in one big formula:
Code:
`=(IF(WEEKDAY(C2,2)=7,C2,C2-WEEKDAY(C2,2)+0)-IF(WEEKDAY(B2,2)=1,B2,B2-WEEKDAY(B2,2)+8)+1)/7`
Hope that helps.

4. ## Re: Calculate the number of full weeks (Mon-Sun) between start of quarter and end of quarter

Originally Posted by Asala42
Here are some formulas that may help.

In D2:
Code:
`=IF(WEEKDAY(B2,2)=1,B2,B2-WEEKDAY(B2,2)+8)`
Will display the next Monday following the start date, if it's not already a Monday.

In E2:
Code:
`=IF(WEEKDAY(C2,2)=7,C2,C2-WEEKDAY(C2,2)+0)`
Will display the previous Sunday from the end date, provided it's not already a Sunday

In F2:
Code:
`=(E2-D2+1)/7`
Will give the number of full weeks, Monday through Sunday, with no fractions.

Or you could put it all together in one big formula:
Code:
`=(IF(WEEKDAY(C2,2)=7,C2,C2-WEEKDAY(C2,2)+0)-IF(WEEKDAY(B2,2)=1,B2,B2-WEEKDAY(B2,2)+8)+1)/7`
Hope that helps.
Thank you so much for splitting the formula out 'I got it immediately' and it works. I knew there was a way. Thank you!!!!

## 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
•