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

bgrove

Board Regular
Joined
Dec 2, 2013
Messages
60
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Code:
[B]Excel 2007 32 bit[/B]
[SIZE=1][TABLE="class: head"]
<tbody>[TR="bgcolor: #FFFFFF"]
[TD]Start[/TD]
[TD]End[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD][RIGHT]1/1/2017[/RIGHT]
[/TD]
[TD][RIGHT]3/31/2017[/RIGHT]
[/TD]
[TD][RIGHT]13[/RIGHT]
[/TD]
[TD][/TD]
[TD]=(B2-A2)/7[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD][RIGHT]4/1/2017[/RIGHT]
[/TD]
[TD][RIGHT]6/30/2017[/RIGHT]
[/TD]
[TD][RIGHT]13[/RIGHT]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD][RIGHT]7/1/2017[/RIGHT]
[/TD]
[TD][RIGHT]9/30/2017[/RIGHT]
[/TD]
[TD][RIGHT]13[/RIGHT]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD][RIGHT]10/1/2017[/RIGHT]
[/TD]
[TD][RIGHT]12/31/2017[/RIGHT]
[/TD]
[TD][RIGHT]13[/RIGHT]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/SIZE]

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

Adjust formula as required for your cell locations.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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!!!!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top