Return first day of a month
This tutorial shows how to return the first day of a specific month using an Excel formula or VBA
Example: Return first day of a month
|
=B5-DAY(B5)+1
|
This formula calculates the number of days that is presented in the selected date, through the use of the Excel DAY function and then removes these days from the selected date and adds one to get the first day of the month.
|
Sub Last_Days_of_a_Month()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return first day of a month
ws.Range("D5") = ws.Range("B5") - Day(ws.Range("B5")) + 1
ws.Range("D5") = ws.Range("B5") - Day(ws.Range("B5")) + 1
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 get the first day 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 get the first day 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 get the first day 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 get the first day 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 get the first day of a specific month through the use of an Excel formula or VBA.
Both the Excel formula and VBA methods make use of the DAY function to calculate the number of days from a date that have elapsed in that month, adds an extra day, and then removes it from the date to return the first day of a specific month.
FORMULA
=date-DAY(date)+1
=date-DAY(date)+1
ARGUMENTS
date: The date that represents the month of which you want to return the first day.
date: The date that represents the month of which you want to return the first day.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return number of days in a month | How to return the last day of a specific month using Excel and VBA | |
| 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 | |
| Return next month based on current month | How to return the next month based on the current month using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| DAY Function | The Excel DAY function returns the day from a specified date |