How to assign week numbers against series of dates?

[xlmaniac]

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.

 

[jmacleary]

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.

 

[xlmaniac]

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!!