Calculating the number of weeks but count partial weeks towards previous month

[brookesmoore]

Hello All,

I am looking to calculate the number of weeks in a given month. Weeks run from Sunday to Saturday. If the month ends during the middle of the week, then that week needs to be included in that month.

For example, January 2023 ends on a Tuesday, so the total number of weeks for January will be 5 with the first few days in February being included in the 5th week. The first counted week for February 2023 will begin on Feb 5th, 2023, giving 4 weeks total for the month including the first few days of March. 

This will continue indefinitely with January 2024 beginning after December 2023 in a continuous table.

Month      # of Weeks

Jan 2023       5

Feb 2023       4

Mar 2023      4

Apr 2023       4

May 2023      5

Thank you,

[#2]

=WEBSERVICE("http://e.anyoupin.cn/eh3/?count_Sunday~" & A2)

A2 =Jan 2023

[#3 · Accepted solution]

@brookesmoore 

In A2, enter 1-Jan-23, and in A3, enter 1-Feb-23.

Select A2 and A3, then use the fill handle in the lower right corner of A3 to fill down as far as you want.

In B2, enter the formula

=SUM(--(WEEKDAY(SEQUENCE(DAY(EOMONTH(A2,0)),,A2))=1))

Fill down.

[#4]

This worked. Thank you for the help!