Display week number starting from week 1 by specific month

[KIM Chanthan]

Hi,

Anyone can help to solve my issue like below question:
Ex: In Aug-2020, there are 6 weeks, So I want it to display 6 week by starting from week 1 to week 6 in each columns.

Thanks,
Chanthan

 

[vletm]

KIM Chanthan
Which day starts You used week?
Do You want full weeks or all weeks possible weeks?

 

[Chihiro]

I assume you use Sun-Sat week (as Aug 1st start on Sat, and you mention 6 weeks in Aug).

Cell to calculate # of partial weeks in a month.

If using Office 365.
Ex: In B1
=SUMPRODUCT(--(SEQUENCE(6,,A1-WEEKDAY(A1,2),7)<=EOMONTH(A1,0)))-(WEEKDAY(A1)=1)

Then in B3 to G3.
=IF(COLUMNS($A$1:A1)<=$B$1,"Week"&COLUMNS($A$1:A1),"")
Copy across.

If using older version of Excel/Office.
Change B1 formula to...
=SUMPRODUCT(--((A1-WEEKDAY(A1,2)+{0,7,14,21,28,35})<=EOMONTH(A1,0)))-(WEEKDAY(A1)=1)

See attached (J1 holds formula for older version).

EDIT: You can combine it into single formula. But for ease of following logic. It's separated into 2 parts.

 

[KIM Chanthan]

I assume you use Sun-Sat week (as Aug 1st start on Sat, and you mention 6 weeks in Aug).

Cell to calculate # of partial weeks in a month.

If using Office 365.
Ex: In B1
=SUMPRODUCT(--(SEQUENCE(6,,A1-WEEKDAY(A1,2),7)<=EOMONTH(A1,0)))-(WEEKDAY(A1)=1)

Then in B3 to G3.
=IF(COLUMNS($A$1:A1)<=$B$1,"Week"&COLUMNS($A$1:A1),"")
Copy across.

If using older version of Excel/Office.
Change B1 formula to...
=SUMPRODUCT(--((A1-WEEKDAY(A1,2)+{0,7,14,21,28,35})<=EOMONTH(A1,0)))-(WEEKDAY(A1)=1)

See attached (J1 holds formula for older version).

EDIT: You can combine it into single formula. But for ease of following logic. It's separated into 2 parts.

Thanks Chihiro. It works perfectly as expected.