nth Week Number in the Month?

FredRock53

New Member
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.
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​

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

sandy666

Banned - Rules violations
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

Power Query:
``````// 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

New Member
Welcome to the MrExcel forum!

Try:

Book1
ABC
1DateDayOccurrence
21/1/2021Friday1
31/2/2021Saturday1
41/3/2021Sunday1
51/4/2021Monday1
61/5/2021Tuesday1
71/6/2021Wednesday1
81/7/2021Thursday1
91/8/2021Friday2
101/9/2021Saturday2
111/10/2021Sunday2
121/11/2021Monday2
131/12/2021Tuesday2
141/13/2021Wednesday2
151/14/2021Thursday2
161/15/2021Friday3
171/16/2021Saturday3
Sheet3
Cell Formulas
RangeFormula
B2:B17B2=TEXT(A2,"dddd")
C2:C17C2=INT((DAY(A2)+6)/7)
This is perfect thank you kindly.
Simple and elegant.
=INT((DAY(A2)+6)/7)

Eric W

MrExcel MVP
Glad we could help. Thanks for the feedback.

FredRock53

New Member
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.

Replies
3
Views
104
Replies
0
Views
77
Replies
3
Views
25
Replies
2
Views
85
Replies
6
Views
56

1,128,135
Messages
5,628,901
Members
416,352
Latest member
Lunox01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back