Excel formula to get week number in month (having

问题:

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.

回答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)

回答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).

回答3:

Finding of week number for each date of a month (considering Monday as beginning of the week)

Keep the first date of month contant $B$13

=WEEKNUM(B18,2)-WEEKNUM($B$13,2)+1

WEEKNUM(B18,2) - returns the week number of the date mentioned in cell B18

WEEKNUM($B$13,2) - returns the week number of the 1st date of month in cell B13