nth Week Number in the Month?

FredRock53

New Member
Joined
Dec 10, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
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....
datedayday 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​
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
Solution

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
maybe
datedateDay NameWeek of YearWeek of Month
01/01/202101/01/2021Friday11
02/01/202102/01/2021Saturday11
03/01/202103/01/2021Sunday11
04/01/202104/01/2021Monday22
05/01/202105/01/2021Tuesday22
06/01/202106/01/2021Wednesday22
07/01/202107/01/2021Thursday22
08/01/202108/01/2021Friday22
09/01/202109/01/2021Saturday22
10/01/202110/01/2021Sunday22
11/01/202111/01/2021Monday33
12/01/202112/01/2021Tuesday33
13/01/202113/01/2021Wednesday33
14/01/202114/01/2021Thursday33
15/01/202115/01/2021Friday33
16/01/202116/01/2021Saturday33
17/01/202117/01/2021Sunday33
18/01/202118/01/2021Monday44
19/01/202119/01/2021Tuesday44
20/01/202120/01/2021Wednesday44
21/01/202121/01/2021Thursday44
22/01/202122/01/2021Friday44

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
Joined
Dec 10, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,294
Messages
5,623,824
Members
415,991
Latest member
RicardoSS

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top