Weeks in a month formula

Hello,
With the work week being Monday to Friday, I would like to know how to count the number of work weeks (5 days) in a month. The weeks that are not complete should return zero. week 0, 1, 2, 3, 4 etc.
Thanks!

Hello Tashia,

Welcome to the Forum!

This macro is a User Defined Function. It works the same as a worksheet formula after you install it. It requires only a valid date as an argument. It will return either a 3 or a 4 indicating the weeks (5 day weeks) in the month.

Function NumberOfWeeks(ByVal D As Date) As Integer

  Dim M As Integer
  Dim RetVal As Integer
  Dim StartDay As Integer
  Dim TotalDays As Integer
  Dim Y As Integer
    
      RetVal = 3
      M = Month(D)
      Y = Year(D)
      StartDay = Weekday(DateSerial(Y, M, 1))
      TotalDays = Day(DateSerial(Y, M + 1, 1) - 1)
  
        Select Case M
          Case 2
            If StartDay = 7 Or StartDay = 1 Or (TotalDays = 29 And StartDay = 6) Then
              RetVal = 4
            End If
          Case Else
            Select Case StartDay
              Case Is = 1, 2, 5, 6, 7
                RetVal = 4
              Case Is = 4
                If TotalDays = 31 Then RetVal = 4
            End Select
        End Select
            
      NumberOfWeeks = RetVal
      
End Function

Example

  'Cell A1 has the date 6/20/2008

  'Cell A2 has the formula...
   =NumberOfWeeks(A1)

  'Cell A2 will show a 4

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

Hello Leith,

If I put the date 1-Feb-2010 in A1 (it's a Monday) I get a result of 3, shouldn't that be 4?

This formula will give you a count for the whole month

=3+(DAY(A1-DAY(A1)+33-WEEKDAY(A1-DAY(A1)-1))>9)

If you want the count to be from the start of the month to the referenced date, e.g. Thu 19-Jun-08 will give 2 but Fri 20-Jun-08 will give 3 try

=MAX(0,INT((DAY(A1)-5+WEEKDAY(A1-DAY(A1)-1))/7))

Thanks for the reply!
I actually need help writing a formula that will give the following results:

Work Week is Monday - Friday

0 = the first partial week (for example the 1st of the month starts on a Tuesday)
1 = the 1st full week
2 = 2nd full week
3 = 3rd full week
4 = 4th full week
5 = if the month ends on a partial week (for example the 31st lands on a Thursday)

Thanks!!