Calculating Weeks in Current Quarter

[kiyo052]

Is it possible to calculate how many weeks are in the current quarter?

For example, if A1= 4, then B2=13.

I was thinking there was an easier way then counting how many weeks each quarter.

Thank you in advance for your help!

 

[barry houdini]

I think you need to define some terms

What's the current quarter 1st October 2017 to 31st December 2017?

Which day does the week start.

How do you handle partial weeks?

 

[kiyo052]

Hi Barry,

Thanks for the quick reply! I actually never thought of those things....

Current quarter would equal 10/1/17 to 12/31/17

The number of weeks should only count work days. Therefore, the first day of the week is Monday 10/2/17

A full week is considered Monday to Friday. I would not count the week if the quarter starts or ends on Wednesday or Thursday.

Let me know if you need any further info!

 

[barry houdini]

If you have the year in A1 e.g. 2017 and quarter number in B1, e.g. 4 then this formula will give you the total number of weeks in that quarter, counting weeks if the Wednesday of that week is within the quarter

=NETWORKDAYS.INTL(DATE(A1,B1*3-2,1),DATE(A1,B1*3+1,0),"1101111")

Using this method ensures that every week is counted in one quarter only.

Unsurprisingly, as every quarter is between 90 and 92 days, this will give you 13 in most cases - only giving 14 if Q3 or Q4 starts on a Wednesday or 12 if Q1 in a non leap year starts on a Thursday