How to Calculate the First Day of the Month Using Excel VBA (3 Methods)

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 corresponding joining date using a simple VBA code. Here’s an overview of the dataset:

excel vba first day of month

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 → 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 → 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 → Run Sub/UserForm.

The code has calculated the first day of the previous month (e.g., 05-Aug-21).

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).

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:

First Day of Month.xlsm

<< Go Back to Excel MONTH Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Function countmonday(ByVal mname As String, ByVal yrvalue As String) As Integer Dim totalcount, given_mnth As Integer Dim given_date As Date given_date = CDate(mname & " 1, " & yrvalue) Select Case Weekday(given_date) Case vbMonday Case vbTuesday given_date = DateAdd("d", 6, given_date) Case vbWednesday given_date = DateAdd("d", 5, given_date) Case vbThursday given_date = DateAdd("d", 4, given_date) Case vbFriday given_date = DateAdd("d", 3, given_date) Case vbSaturday given_date = DateAdd("d", 2, given_date) Case vbSunday given_date = DateAdd("d", 1, given_date) End Select given_mnth = Month(given_date) Do totalcount = totalcount + 1 given_date = DateAdd("ww", 1, given_date) Loop While (Month(given_date) = given_mnth) countmonday = totalcount End Function

4 Comments

Alphonse Feb 4, 2023 at 8:55 PM

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.
- Reply
  Tanjima Hossain Feb 6, 2023 at 2:50 PM
  Hello Alphonse,
  Thanks for your appreciation.
  You can try the code below to count number of Mondays for a month of a year
```
Function countmonday(ByVal mname As String, ByVal yrvalue As String) As Integer
Dim totalcount, given_mnth As Integer
Dim given_date As Date
given_date = CDate(mname & " 1, " & yrvalue)
    Select Case Weekday(given_date)

        Case vbMonday
        Case vbTuesday
            given_date = DateAdd("d", 6, given_date)
        Case vbWednesday
            given_date = DateAdd("d", 5, given_date)
        Case vbThursday
            given_date = DateAdd("d", 4, given_date)
        Case vbFriday
            given_date = DateAdd("d", 3, given_date)
        Case vbSaturday
            given_date = DateAdd("d", 2, given_date)
        Case vbSunday
            given_date = DateAdd("d", 1, given_date)
    End Select
    given_mnth = Month(given_date)
    Do
        totalcount = totalcount + 1
        given_date = DateAdd("ww", 1, given_date)
    Loop While (Month(given_date) = given_mnth)
    countmonday = totalcount
End Function
```
  • 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
Alphonse Apr 10, 2024 at 9:06 AM

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.
- Reply
  Shamima Sultana Apr 16, 2024 at 2:56 PM
  
  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