Return number of days in a month
This tutorial shows how to return the number of days in a specific month using an Excel formula or VBA
Example: Return number of days in a month
|
=DAY(EOMONTH(B5,0))
|
This formula uses a combination of Excel DAY and EOMONTH functions to calculate the number of days that are in a specific. The EOMONTH function, with the months criteria of 0, returns the last day for the month, represented as a date. Therefore in this example it would return a date of 31 March 2019. Then the DAY function is used to extract only the day from the selected date, which in this example would be the last day of the month.
|
Sub Days_in_a_Month()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the number of days in a month
ws.Range("D5") = Day(Application.WorksheetFunction.EoMonth(ws.Range("B5"), 0))
ws.Range("D5") = Day(Application.WorksheetFunction.EoMonth(ws.Range("B5"), 0))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Month: Select the date that represents the month for which you want to return the number of days that form part of that month by changing the cell reference ("B5"), in the VBA code, or enter the date with the relevant month in cell ("B5").
Worksheet Selection: Select the worksheet which captures the date that represents the month for which you want to return the number of days that form part of that month by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("D5") in the VBA code.
Month: Select the date that represents the month for which you want to return the number of days that form part of that month by changing the cell reference ("B5"), in the VBA code, or enter the date with the relevant month in cell ("B5").
Worksheet Selection: Select the worksheet which captures the date that represents the month for which you want to return the number of days that form part of that month by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to return the number of days in a specific month through the use of an Excel formula or VBA.
Both the Excel formula and VBA methods make use of the DAY and EOMONTH functions to return the number of days in a specific month.
FORMULA
=DAY(EOMONTH(date,0))
=DAY(EOMONTH(date,0))
ARGUMENTS
date: The date that represents the month of which you want to return the number of days.
date: The date that represents the month of which you want to return the number of days.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| 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 | |
| Difference in days between two dates | How to calculate the difference in days 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 |