Excel Calculate week of Month

Question

Hi,

I've been trying to get week of month in excel, I've found this formula

WEEKNUM(C4,1)-WEEKNUM(DATE(YEAR(C4),MONTH(C4),1),1)+1

but this gives

Where 26th weekday 1 should be start of new week? week 1 not 5 , am I missing something?

Answer 1

For a Sunday, the WEEKDAY function by default returns 1, since Sunday is the first day of the week in the USA.

You can add a second argument if the week should start on another day; for example in the European Union, the week begins on Monday. Use WEEKDAY(date, 2) to return the day number taking Monday as the first day of the week.

The WEEKNUM function returns the number of the week within the year, again by default using the USA system of week numbering,

You can add a second argument for other systems.

Recent versions of Excel also have a function ISOWEEKNUM that returns the week number according to the ISO system in use in the European Union.

Excel does not have a built-in method to return the week number within the month. It is not difficult to create a formula for that, but you have to define how you want to count the week number within a month.

For example:

Week 1 starts on the 1st day of the month, week 2 on the 8th, week 3 on the 15th, etc.

or:

Week 1 starts on the 1st day of the month, week 2 on the first Sunday after that, etc.

or:

Week 1 starts on the 1st day of the month, week 2 on the first Monday after that, etc.

or:

Week 1 starts on the first Monday of the month; days before that belong to the last week of the previous month.

and so on...

Answer 2

Can you explain why the week number of 26/01/2020 should be 1?

Answer 3

Hi Hans,

Yes,

didn't really explain very well, my thinking was if 26th is a Sunday, I should be starting a new week,

so for the 26 the week of the year is the 5th, so thought, wrongly? , that it should start again at 1.

I've spent most of the afternoon, going down a rabbit hole of date formulas, I'm going out for walk.

RD