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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to excel-2007

Examples related to excel-formula

Examples related to excel-2010