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
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.
|
Sub Days_in_a_Current_Month()
'declare a variable
Dim ws As Worksheet
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))
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.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
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))
=DAY(EOMONTH(NOW(),0))
RELATED TOPICS
Related Topic | Description | Related Topic and Description |
---|---|---|
Return number of days in a month | How to return the number of days in a specific month using Excel and VBA | |
Return previous month based on current month | How to return the previous month based on the current month using Excel and VBA methods | |
Return next month based on current month | How to return the next month based on the current month using Excel and VBA | |
Difference in months between two dates | How to calculate the difference in months between two dates using Excel and VBA methods |
RELATED FUNCTIONS
Related Functions | Description | Related Functions and Description |
---|---|---|
DAY Function | The Excel DAY function returns the day from a specified date | |
NOW Function | The Excel NOW function returns the current date and time |