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

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

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

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