Formula to count the week from the first weekday of the month

[InvaderZIM05]

Hello!

I am a newbie in terms of excel, but I would like your help in coming up with a formula so that I can count the weeks of the month starting from the first weekday of the month.

Example, is this month of June 2024, the first day is June 1 which falls on a Saturday counts as the first week of the month while June 2 counts as the second week when using the WEEKNUM function. I want to figure out a way to make it only start counting on the first WEEKDAY of the month which in this case is June 3 as the first week of the month.

Thank you in advanced for your help.

 

[Phuoc]

The first Monday of the month:

=WORKDAY.INTL(EOMONTH(A1,-1),1,"0111111")

 

[Tetra201]

Here is a formula for you to try. It assigns week number 0 to Saturday, June 1, 2024 and Sunday, June 2, 2024.

=IFERROR(LOOKUP(A2,WORKDAY.INTL(WORKDAY(EOMONTH(A2,-1),1),{0,1,2,3,4},"0111111"),{1,2,3,4,5}),0)

 

[Phuoc]

Or try this:

Book1
	A	B
1
2	1/6/2024	0
3	2/6/2024	0
4	3/6/2024	1
5	19/6/2024	3
6	30/6/2024	4
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=NETWORKDAYS.INTL(EOMONTH(A2,-1)+1,A2,"0111111")

 

[InvaderZIM05]

Thank you all for your help! I am now able to get the results needed.
I appreciate your prompt responses.

 

[Tetra201]

Please note that the formulas offered in Posts #3 and #4 return different results:
-The formula from Post #3 assigns week number 1 to the first weekday of the month -- as requested in the OP. Thus, for example, Thursday, August 1, 2024 is week 1.
-The formula from Post #4 assigns week number 1 to the first Monday of the month. Thus, for example, the first weekday of August (Thursday, Aug 1, 2024) is week 0.

 

[InvaderZIM05]

Please note that the formulas offered in Posts #3 and #4 return different results:
-The formula from Post #3 assigns week number 1 to the first weekday of the month -- as requested in the OP. Thus, for example, Thursday, August 1, 2024 is week 1.
-The formula from Post #4 assigns week number 1 to the first Monday of the month. Thus, for example, the first weekday of August (Thursday, Aug 1, 2024) is week 0.

Thank you for confirming.

I ran into some new challenges.
Do we have a function to get the first weekday of the week, removing the previous months days?

For example:

May 3, 2024 (Friday) will return May 1, 2024 (Wednesday)
May 7, 2024 (Tuesday) will return May 6, 2024 (Monday)

	A	B
1	Audit Date	Week Start
2	Friday - May 03, 2024	Wednesday - May 01, 2024
3	Tuesday - May 07, 2024	Monday - May 06, 2024

 

[Tetra201]

See if the following formula works for you:

=MAX(WORKDAY.INTL(A2+1,-1,"0111111"),WORKDAY(EOMONTH(A2,-1),1))

 

[InvaderZIM05]

See if the following formula works for you:

=MAX(WORKDAY.INTL(A2+1,-1,"0111111"),WORKDAY(EOMONTH(A2,-1),1))

This worked great! Thank you so much.