Return number of days in a current month

This tutorial shows how to return the number of days in a current month using an Excel formula or VBA

Example: Return number of days in a current month

Return number of days in a current month

METHOD 1. Return number of days in a current month using Excel formula

EXCEL

=DAY(EOMONTH(NOW(),0))
This formula uses a combination of Excel DAY, EOMONTH and NOW functions to calculate the number of days that are in a current month. Using the NOW function inside the EOMONTH function, with the months criteria of 0, this part of the formula will return the last day in the current month, in date format. Then using the DAY function the formula will extract only the day from the date which would be the last day in the current month.

METHOD 1. Return number of days in a current month using VBA

VBA

Sub Days_in_a_Current_Month()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the number of days in a current month
ws.Range("D5") = Day(Application.WorksheetFunction.EoMonth(Now, 0))

End Sub

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.

Explanation about how to extract the number of days in a current month

EXPLANATION

EXPLANATION

This tutorial shows how to return the number of days in a current month through the use of an Excel formula or VBA.
Both the Excel formula and VBA methods make use of the DAY, EOMONTH and NOW functions to return the number of days in the current month.
FORMULA
=DAY(EOMONTH(NOW(),0))

RELATED TOPICS

Related Topic Description Related Topic and Description
How to return the number of days in a specific month using Excel and VBA
How to return the previous month based on the current month using Excel and VBA methods
How to return the next month based on the current month using Excel and VBA
How to calculate the difference in months between two dates using Excel and VBA methods

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel DAY function returns the day from a specified date
The Excel NOW function returns the current date and time