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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
U
  • Question
Week number based on the year starting with a particular date
Replies
2
Views
365
Jeffrey Mahoney
Jeffrey Mahoney
A
  • Question
Creating a Calendar for a Month with tasks
Replies
0
Views
136
anuradhagrewal
A
J
  • Solved
Converting Month to Weeks from the current date
Replies
8
Views
223
jajatidev
J
M
  • Question
Excel - Identify number of business days for a specific month within a weekly date range
Replies
3
Views
500
duggie33
D

Forum statistics

Threads
1,215,076
Messages
6,122,987
Members
449,093
Latest member
Mr Hughes

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