How to assign week numbers against series of dates?

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
527
Office Version
  1. 2010
Platform
  1. Windows
Dear All,
I do have a huge data base spread across thousands of rows.
Out of this data-set, there is one column which contains the dates from October 2018 to December 2018 in a random order.
I would like to assign numbers against the days within every month as follows:-

October

01/10/2018-Monday-Formula Should Return 1 against this date (since it is 1st Monday for October)
08/10/2018-Monday-Formula Should Return 2 against this date (since it is 2nd Monday for October) & so on(3,4,5) for Mondays.

Similarly it should return the same 1,2,3,4 & 5(if applicable) against 2nd,9th,16th,23rd,30th October(Tuesday).

For Rest of the days of the week of October, it should yield the same in a sequential order.

Same logic should be extended to Days in November and the formula should populate 1,2,3,4,5 against the dates/days.

Pls help.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi there.

Assuming your dates start in A2 then this formula will do it: =INT((DAY(A2)-1)/7)+1 - just put it in and drag down as needed.
 
Upvote 0
Dear jmacleary,
Thanks a lot for the amazing solution!!
You have saved tons of man hour for me.
Pls accept my sincere gratitude for the same!!:)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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