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

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.
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
74
Fluff
Fluff
D
  • Question Question
Divide Current Month into weeks
Replies
3
Views
334
Armstrong_N14
A
L
  • Solved
Fix calendar setup
Replies
7
Views
96
MARK858
MARK858
J
  • Solved
Daily 3yr CF: Need to flag 4th/last Thursday of each month
Replies
3
Views
239
Fluff
Fluff
I
  • Solved
Formula to count the week from the first weekday of the month
Replies
8
Views
484
InvaderZIM05
I

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

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