VBA Tip: How Many Days in a Month

You can use the following formula to calculate the number of days in a month or determine the last day in a month:

Sub nb_days_month()
    
    date_test = CDate("6/2/2012") 'Any date will do for this example

    nb_days = Day(DateSerial(Year(date_test), Month(date_test) + 1, 1) - 1)
    
End Sub

Here is a version of the formula broken down into its constituent parts with commentaries that will help you understand how it works:

Sub nb_days_month()
    
    'Any date will do for this example
    date_test = CDate("6/2/2012")
    
    'Month / Year of the date
    var_month = Month(date_test)
    var_year = Year(date_test)
    
    'Calculation for the first day of the following month
    date_next_month = DateSerial(var_year, var_month + 1, 1)
    
    'Date of the last day
    last_day_month = date_next_month - 1
    
    'Number for the last day of month (= last day)
    nb_days = Day(last_day_month)
    
End Sub

Function

You can also use the above code as a function by adding the following code to a module:

Function NB_DAYS(date_test As Date)
    NB_DAYS = Day(DateSerial(Year(date_test), Month(date_test) + 1, 1) - 1)
End Function

Example of how the function can be used in VBA:

Sub example()
    test = NB_DAYS(Range("A1"))
    MsgBox test
End Sub

Example of how the function can be used on a calculation worksheet: