The Numbered Week of the Month

C

Chiroptera

New Member
Joined
Jan 4, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
So I have data that spans every week for an entire year. What I want to do is make a formula that shows if this date is the first, second, third, fourth, or fifth time that week appears in that month. (Assume each month is a list of 1-4 or 1-5. Where is the week on the list?)

(Weeknum doesn't work-- I want an output of 1-5 only, whereas that gives me 1-52).


So for example, here's a basic list:


1704399253000.png


Column A is what I have and column B is what I want to do (via formula not manually).

Since 1/1/2017 is the first date on this list that appears in the month of January, it should appear as 1. But since 2/5/2017 is the first date that appears in February, it's ALSO got a value of 1.
You'll see 4 week months and 5 week months.

Any ideas?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
With Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type)
in
    #"Inserted Week of Month"

DateDateWeek of Month
1/1/20241/1/20241
1/8/20241/8/20242
1/15/20241/15/20243
1/22/20241/22/20244
1/29/20241/29/20245
2/5/20242/5/20242
2/12/20242/12/20243
2/19/20242/19/20244
2/26/20242/26/20245
3/4/20243/4/20242
3/11/20243/11/20243
3/18/20243/18/20244
3/25/20243/25/20245
4/1/20244/1/20241
4/8/20244/8/20242
4/15/20244/15/20243
4/22/20244/22/20244
4/29/20244/29/20245
5/6/20245/6/20242
5/13/20245/13/20243
5/20/20245/20/20244
5/27/20245/27/20245
6/3/20246/3/20242
6/10/20246/10/20243
6/17/20246/17/20244
6/24/20246/24/20245
7/1/20247/1/20241
7/8/20247/8/20242
7/15/20247/15/20243
7/22/20247/22/20244
7/29/20247/29/20245
8/5/20248/5/20242
8/12/20248/12/20243
8/19/20248/19/20244
8/26/20248/26/20245
9/2/20249/2/20241
9/9/20249/9/20242
9/16/20249/16/20243
9/23/20249/23/20244
9/30/20249/30/20245
10/7/202410/7/20242
10/14/202410/14/20243
10/21/202410/21/20244
10/28/202410/28/20245
11/4/202411/4/20242
11/11/202411/11/20243
11/18/202411/18/20244
11/25/202411/25/20245
12/2/202412/2/20241
12/9/202412/9/20242
12/16/202412/16/20243
12/23/202412/23/20244
12/30/202412/30/20245
 
Upvote 0
The following formula returns the expected results for the posted dataset:
Excel Formula: 
=NETWORKDAYS.INTL(EOMONTH(A2,-1)+1,A2,"1111110")
 
Upvote 0
Solution
This seems to give the same results.

EXCEL
JKLM
11/1/2017Sun11
21/8/2017Sun22
31/15/2017Sun33
41/22/2017Sun44
51/29/2017Sun55
62/5/2017Sun11
72/12/2017Sun22
82/19/2017Sun33
92/26/2017Sun44
103/5/2017Sun11
113/12/2017Sun22
123/19/2017Sun33
133/26/2017Sun44
144/2/2017Sun11
154/9/2017Sun22
164/16/2017Sun33
174/23/2017Sun44
184/30/2017Sun55
Data
Cell Formulas
RangeFormula
M1:M18M1=ROUNDUP(DAY(J1:J18)/7,0)
L1:L18L1=NETWORKDAYS.INTL(EOMONTH(J1,-1)+1,J1,"1111110")
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
monthly payments in weekly model
Replies
5
Views
55
Fluff
Fluff
D
  • Question Question
Divide Current Month into weeks
Replies
3
Views
305
Armstrong_N14
A
J
  • Solved
Daily 3yr CF: Need to flag 4th/last Thursday of each month
Replies
3
Views
205
Fluff
Fluff
I
  • Solved
Formula to count the week from the first weekday of the month
Replies
8
Views
457
InvaderZIM05
I
C
  • Solved
First Monday of week one (not first monday of month)
Replies
4
Views
572
JEC
JEC

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

Share this page

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
Back
Top