Excel formula to get week number in month (having Monday)

Question

Using excel formula I need to get week number in month from a given date. But, the condition is it should have Monday in it. Monday through Sunday is the work days.

I have tried this:

But, week number is given as 5, where as it should be 4 because 1st November 2013 was Friday, so it would be calculated in October's last week.

Solution 1

If week 1 always starts on the first Monday of the month try this formula for week number

=INT((6+DAY(A1+1-WEEKDAY(A1-1)))/7)

That gets the week number from the date in A1 with no intermediate calculations - if you want to use your "Monday's date" in B1 you can use this version

=INT((DAY(B1)+6)/7)

Solution 2

Jonathan from the ExcelCentral forums suggests:

=WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),2)+1 

This formula extracts the week of the year [...] and then subtracts it from the week of the first day in the month to get the week of the month. You can change the day that weeks begin by changing the second argument of both WEEKNUM functions (set to 2 [for Monday] in the above example). For weeks beginning on Sunday, use:

=WEEKNUM(A1,1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),1)+1

For weeks beginning on Tuesday, use:

=WEEKNUM(A1,12)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),12)+1

etc.

I like it better because it's using the built in week calculation functionality of Excel (WEEKNUM).

Comments

[Nuno Furtado]

Thx finally found what i needed.

[Dave Bruns]

Great formula. Can you explain the logic used to create a number that can be divided by 7?

[Monero Jeanniton]

Thank you! very helpful