VBA Tip: Number of 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 nbDaysMonth()
    
    testDate = CDate("2/6/2024") 'Any date will do for this example

    nbDays = Day(DateSerial(Year(testDate), Month(testDate) + 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 nbDaysMonth()
    
    'Any date will do for this example
    testDate = CDate("2/6/2024")
    
    'Month / Year of the date
    varMonth = Month(testDate)
    varYear = Year(testDate)
    
    'Calculation for the first day of the following month
    dateNextMonth = DateSerial(varYear, varMonth + 1, 1)
    
    'Date of the last day
    lastDayMonth = dateNextMonth - 1
    
    'Number for the last day of month (= last day)
    nbDays = Day(lastDayMonth)
    
End Sub

Function

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

Function nbDays(testDate As Date)
    nbDays = Day(DateSerial(Year(testDate), Month(testDate) + 1, 1) - 1)
End Function

Example of how the function can be used in VBA:

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

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