Week of the month (1 to 5)

[mameliuka]

Hi,

I am not able to find a solution to the following problem:
I need a formula to define which week of the month the date is, from 1st to 5th. With the assumption that if there is a week where there are more than 4 days from the first days of a month - it is a 1st week (like June 2017 for example, 1st June starts on Thursday, so it should be counted as 1st week for June, and not counted for May 2017 as it includes less than 3 May days). Googled a lot, haven't found suitable solution... Hope you can help guys!

[Special-K]

"where there are more than 4 days from the first days of a month"
There are ALWAYS more than 4 days from the first day of a month, can you be more specific?

Attach a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[mameliuka]

"where there are more than 4 days from the first days of a month" - for example 29th-31st of May 2017 are Monday - Wednesday, 1st-4th June - Thursday-Friday, so June has 4 days that week and therefore it is going to be counted as 1st week of June. If there were 4 days for May - Monday-Thursday or more - it would be May 5th week.
Please see example attached. I need numbers in column E to be represented by formulas

[Pete_UK]

You seem to be defining a week as Monday to Sunday, and if the first of a month begins on a Monday to Thursday (inclusive) then that should be counted as week 1 for the month. You can test for that condition like this:

=IF(WEEKDAY(first_of_month,2)<5, ...

you can get the first_of_month from a date (in B3) like this:

EOMONTH(B3,-1)+1

or even:

DATE(YEAR(B3),MONTH(B3),1)

Hope this helps.

Pete

[skyping]

Hi,

Please see attached which I think solves your problem.

Change cell A2 only to see results in Col F.

Hope this helps

Skyping

[Excelski]

=weeknum(b3,2)-weeknum(eomonth(b3,-1)+1)