How to find what week of the Month a date falls

[Pookiemeister]

I would like to find some code that shows what week of a particular date falls.
Example: Using today's date mm/dd/yyyy format: 05/09/2024 Looking at a calendar, that date falls on the second week of the month. If I use 05/28/2024 that falls on the fifth week of the Month. Is there code that will show the week of that month? Thank you.

 

[Eric W]

2 options, depending on how you define the start of a week:

Book1
	A	B	C	D
1	5/9/2024	2		Weeks are: 1-7, 8-14, 15-21, 22-28, 29-end
2	5/28/2024	4
3
4	5/9/2024	2		Weeks start on Sunday
5	5/28/2024	5
Sheet2

Cell Formulas
Range	Formula
B1:B2	B1	=INT((DAY(A1)-1)/7)+1
B4:B5	B4	=INT((DAY(A4)+WEEKDAY(EOMONTH(A4,-1)+1)-2)/7)+1

 

[Rick Rothstein]

@Eric W - Just wondering if you have something against using Excel's built-in WeekNum function?

 

[Eric W]

@Eric W - Just wondering if you have something against using Excel's built-in WeekNum function?

Well, first, I didn't think of it!

Next, doesn't it give the week from the start of the year, not the start of the month?

 

[Rick Rothstein]

Whoops! I read too fast and missed the part about it being for the beginning of the month as opposed to the beginning of the year.

 

[Tetra201]

Here we go with the WeekNum (for weeks starting on Sunday, will only work within the same year):

=WEEKNUM(A1,1)-WEEKNUM(EOMONTH(A1,-1)+1,1)+1