nth Week Number in the Month?

[FredRock53]

I am having difficulty describing what I need a formula to display.
It's probably why I can'y get my head around how to do it.
Take 1/1/2021 as a starting point it is the first Friday of the month. 8 is the second Friday and 15 is the third. In the same month and year the first Monday is the 4th and the second Monday is the 11th and so on. So on any given date I need to know weather it is the First, Second, Third, Fourth or Fifth occurrence of its particular day of the week.
I am not talking about this... =WEEKNUM(A2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),1))+1
It just gives the week number the day falls in regardless of the day that starts the week.
I was poking around with =WEEKDAY((EOMONTH(A2,-1))) to return the day of the week the month starts on.
But I can't get it to do what i need....

date	day	day occurrence
1/1/2021​	Friday​	1​
1/2/2021​	Saturday​	1​
1/3/2021​	Sunday​	1​
1/4/2021​	Monday​	1​
1/5/2021​	Tuesday​	1​
1/6/2021​	Wednesday​	1​
1/7/2021​	Thursday​	1​
1/8/2021​	Friday​	2​
1/9/2021​	Saturday​	2​
1/10/2021​	Sunday​	2​
1/11/2021​	Monday​	2​
1/12/2021​	Tuesday​	2​
1/13/2021​	Wednesday​	2​
1/14/2021​	Thursday​	2​
1/15/2021​	Friday​	3​
1/16/2021​	Saturday​	3​
1/17/2021​	Sunday​	3​
1/18/2021​	Monday​	3​
1/19/2021​	Tuesday​	3​
1/20/2021​	Wednesday​	3​
1/21/2021​	Thursday​	3​
1/22/2021​	Friday​	4​

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C
1	Date	Day	Occurrence
2	1/1/2021	Friday	1
3	1/2/2021	Saturday	1
4	1/3/2021	Sunday	1
5	1/4/2021	Monday	1
6	1/5/2021	Tuesday	1
7	1/6/2021	Wednesday	1
8	1/7/2021	Thursday	1
9	1/8/2021	Friday	2
10	1/9/2021	Saturday	2
11	1/10/2021	Sunday	2
12	1/11/2021	Monday	2
13	1/12/2021	Tuesday	2
14	1/13/2021	Wednesday	2
15	1/14/2021	Thursday	2
16	1/15/2021	Friday	3
17	1/16/2021	Saturday	3
Sheet3

Cell Formulas
Range	Formula
B2:B17	B2	=TEXT(A2,"dddd")
C2:C17	C2	=INT((DAY(A2)+6)/7)

 

[sandy666]

maybe

date		date	Day Name	Week of Year	Week of Month
01/01/2021		01/01/2021	Friday	1	1
02/01/2021		02/01/2021	Saturday	1	1
03/01/2021		03/01/2021	Sunday	1	1
04/01/2021		04/01/2021	Monday	2	2
05/01/2021		05/01/2021	Tuesday	2	2
06/01/2021		06/01/2021	Wednesday	2	2
07/01/2021		07/01/2021	Thursday	2	2
08/01/2021		08/01/2021	Friday	2	2
09/01/2021		09/01/2021	Saturday	2	2
10/01/2021		10/01/2021	Sunday	2	2
11/01/2021		11/01/2021	Monday	3	3
12/01/2021		12/01/2021	Tuesday	3	3
13/01/2021		13/01/2021	Wednesday	3	3
14/01/2021		14/01/2021	Thursday	3	3
15/01/2021		15/01/2021	Friday	3	3
16/01/2021		16/01/2021	Saturday	3	3
17/01/2021		17/01/2021	Sunday	3	3
18/01/2021		18/01/2021	Monday	4	4
19/01/2021		19/01/2021	Tuesday	4	4
20/01/2021		20/01/2021	Wednesday	4	4
21/01/2021		21/01/2021	Thursday	4	4
22/01/2021		22/01/2021	Friday	4	4

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Date = Table.TransformColumnTypes(Source,{{"date", type date}}),
    Day = Table.AddColumn(Date, "Day Name", each Date.DayOfWeekName([date]), type text),
    WeekY = Table.AddColumn(Day, "Week of Year", each Date.WeekOfYear([date]), Int64.Type),
    WeekM = Table.AddColumn(WeekY, "Week of Month", each Date.WeekOfMonth([date]), Int64.Type)
in
    WeekM

 

[FredRock53]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C
1	Date	Day	Occurrence
2	1/1/2021	Friday	1
3	1/2/2021	Saturday	1
4	1/3/2021	Sunday	1
5	1/4/2021	Monday	1
6	1/5/2021	Tuesday	1
7	1/6/2021	Wednesday	1
8	1/7/2021	Thursday	1
9	1/8/2021	Friday	2
10	1/9/2021	Saturday	2
11	1/10/2021	Sunday	2
12	1/11/2021	Monday	2
13	1/12/2021	Tuesday	2
14	1/13/2021	Wednesday	2
15	1/14/2021	Thursday	2
16	1/15/2021	Friday	3
17	1/16/2021	Saturday	3
Sheet3

Cell Formulas
Range	Formula
B2:B17	B2	=TEXT(A2,"dddd")
C2:C17	C2	=INT((DAY(A2)+6)/7)

This is perfect thank you kindly.
Simple and elegant.
=INT((DAY(A2)+6)/7)

 

[Eric W]

Glad we could help. Thanks for the feedback.

 

[FredRock53]

Glad we could help. Thanks for the feedback.

I added just a bit more =CHOOSE(INT((DAY(C2)+6)/7),"1st","2nd","3rd","4th","5th") so it is easier to read.