# 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: 