Dataset Overview
Suppose we have an Excel worksheet containing information about a sales representative named Emma from the Armani group. Her joining date is provided in column C. Our task is to calculate the first day of the month for the corresponding joining date using a simple VBA code. Here’s an overview of the dataset:
Method 1 – Calculate the First Day of the Current Month in Excel Using VBA
- Select the continuous data rows from your dataset.
- Go to the Developer tab and click on Visual Basic.
- In the Microsoft Visual Basic for Applications window, insert a module by going to Insert and selecting Module.
- Enter the following VBA code in the module:
Sub First_Day_Of_Month()
strDate = DateValue(Range("C5"))
Range("D5") = DateSerial(Year(strDate), Month(strDate), 1)
End Sub
- Run the code by going to Run and clicking on Run Sub/UserForm.
- This will calculate the first day of Emma’s joining date (e.g., 05-Aug-21).
Method 2 – Get the First Day of the Previous Month with Excel VBA
- Insert a new module.
- Insert the following VBA code:
Sub First_Day_Of_Previous_Month()
strDate = DateValue(Range("C5"))
Range("D5") = DateSerial(Year(strDate), Month(strDate) - 1, 1)
End Sub
- Run the code. To do that, go to Run and select Run Sub/UserForm.
- The code has calculated the first day of the previous month (e.g., 05-Aug-21).
Read More: How to Calculate First Day of Previous Month in Excel
Method 3 – Calculate the First Day of the Next Month Using VBA
- Insert another new module.
- Enter the following VBA code:
Sub First_Day_Of_Next_Month()
strDate = DateValue(Range("C5"))
Range("D5") = DateSerial(Year(strDate), Month(strDate) + 1, 1)
End Sub
- Run the code to calculate the first day of the next month.
- The code has calculated the first day of the next month (e.g., 05-Aug-21).
Read More: How to Get First Day of Month from Month Name in Excel
Bonus: Calculate the Last Day of the Current Month Using VBA
Use the EOMONTH function in a VBA code to get the last day of the current month. Here’s the code:
Sub LastDayOfCurrentMonth()
Dim LDay As Double
LDay = Application.WorksheetFunction.EoMonth(Range("C5"), "0")
Range("D5") = VBA.Format(LDay, "mm/dd/yyyy")
End Sub 
Code Explanation:
- We set 0 as the LDay to get the last day of the current month.
- The VBA Format function is used to display the desired date format (e.g., “dd/mm/yyyy”).
- Press F5 to run the code.
- You will get 31-08-21 as the last day of the corresponding date.
Related Content: How to Get Last Day of Previous Month in Excel
Things to Remember
- To open the Microsoft Visual Basic for Applications window, press Alt + F11 simultaneously.
- If the Developer tab is not visible in your ribbon, you can make it visible by following these steps:
- Go to File → Options → Customize Ribbon.
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
- Excel Formula for Current Month and Year
- Excel Formula to Find Date or Days for Next Month
- How to Convert Month to Number in Excel
- Convert 3 Letter Month to Number in Excel
- Excel VBA: First Day of Month
<< Go Back to Excel MONTH Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Good day, sir.
Thank you for a very instructive and helpful article.
I have one comment about English wording, though, perhaps an error in translation.
If you were to count the first, the last, or any specific date in any month, the answer will always be 1. There is only one of each date in each month. We could count Mondays, but not a numeric date.
If we Get any of the above, we will get a date.
I mention this because I was searching for “Count number of Mondays in a specific month” and that brought me to your web page. I still benefitted from it, even if it wasn’t what I was looking for… yet.
Again, thank you for your time in providing us with this info.
Hello Alphonse,
Thanks for your appreciation.
You can try the code below to count number of Mondays for a month of a year
• Then, type the function name and enter the month name and year to count Mondays.
As a result, we are getting 5 which represents Mondays of January 2023.
Best Regards
ExcelDemy
Hello again,
I came back to “visit” and noticed that the message I wrote you thanking you for the code to count Mondays–which worked great–did not actually post; I was having some weird problems with my PC back then. Well, a year later I still want to thank you for giving me the solution to my dilemma at the time.
Cheers.
Hello Alphonse,
We are glad to hear from you again! Don’t worry about missed message, technical glitches happen to the best of us. We are glad to hear that the code for counting Mondays worked well for you. Thank you so much for expressing your gratitude—it’s always means a lot to us to know when something was helpful. You are most welcome and cheers to you too.
Regards
ExcelDemy