Excel VBA: First Day of Month (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with Excel, sometimes we need to know the first day of the given date or month. But there is no direct function or formula in Excel to generate the first day of month from the date or the month name. We can easily calculate the first day of the month by using the VBA Macros. Today, in this article, we’ll learn four quick and suitable ways how to calculate the first day of the month by using the VBA code in Excel effectively.


VBA to Count the First Day of Month (Quick View)

Sub First_Day_Of_Month()
strDate = DateValue(Range("C5"))
Range("D5") = DateSerial(Year(strDate), Month(strDate) - 1, 1)
Range("D6") = DateSerial(Year(strDate), Month(strDate) + 1, 1)
Range("D7") = DateSerial(Year(strDate), Month(strDate), 1)
End Sub

excel vba first day of month


Excel VBA to Get the First Day of Month: 3 Suitable Ways

Let’s consider a situation where we have an Excel worksheet that contains information about a sales representative named Emma of the Armani group. Her joining date is given in column C. We will calculate the first day of the corresponding joining date by using the simple VBA code. Here’s an overview of the dataset for today’s task.

excel vba first day of month


1. Develop a VBA Code to Count the First Day of Current Month in Excel

From our dataset, we will count the first day of the month of the corresponding date by using a simple VBA code. It’s very helpful for some particular moments. Let’s follow the instructions below to learn!

Step 1:

  • First of all, select the continuous data rows that you want to select from your dataset, and then from your Developer tab, go to,

Developer → Visual Basic

Develop a VBA Code to Count the First Day of Current Month in Excel

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – First Day will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

  • Hence, the First Day module pops up. In the First Day module, write down the below VBA code.
Sub First_Day_Of_Month()
strDate = DateValue(Range("C5"))
Range("D5") = DateSerial(Year(strDate), Month(strDate), 1)
End Sub

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

Develop a VBA Code to Count the First Day of Current Month in Excel

  • After running the code, you will be able to count the first day of the joining date as 05-Aug-21 of Emma which has been given in the below screenshot.

Develop a VBA Code to Count the First Day of Current Month in Excel

Read More: Get the First Day of the Current Month in Excel


2. Get the First Day of the Previous Month with Excel VBA

In this method, we will count the first day of the previous month by using the VBA code. This is the easiest and most time-saving way. Let’s follow the steps below to learn!

Step 1:

  • According to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,
Sub First_Day_Of_Previous_Month()
strDate = DateValue(Range("C5"))
Range("D5") = DateSerial(Year(strDate), Month(strDate) - 1, 1)
End Sub

Get the First Day of the Previous Month with Excel VBA

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • While running the code, you will be able to count the first day of the previous month of the joining date as 05-Aug-21 of Emma which has been given in the below screenshot.

Get the First Day of the Previous Month with Excel VBA

Read More: How to Calculate First Day of Previous Month in Excel


3. Count the First Day of the Next Month by Running a VBA Code in Excel

After learning how to calculate the first day of the month of a corresponding date and the first day of the previous day of a corresponding date, now, we will count the first day of the next month by using the VBA code. This is the easiest and time-saving way also. Let’s follow the instructions below to learn!

Step 1:

  • Similarly, according to method 1, insert a new module and type the below VBA code to delete cells. The VBA code is,
Sub First_Day_Of_Next_Month()
strDate = DateValue(Range("C5"))
Range("D5") = DateSerial(Year(strDate), Month(strDate) + 1, 1)
End Sub

Count the First Day of the Next Month by Running a VBA Code in Excel

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • While running the code, you will be able to count the first day of the next month of the joining date as 05-Aug-21 of Emma which has been given in the below screenshot.

Count the First Day of the Next Month by Running a VBA Code in Excel

Read More: How to Get First Day of Month from Month Name in Excel


Bonus: Run a VBA Code to Count the Last Day of the Month in Excel

We will use the EOMONTH function in a VBA code to get the last day of the current month. Let’s follow the instructions below.

Steps:

  • Put the following code in the visual basic editor to get the last day of the current month in cell C5 of a specific date (2nd Feb 2022) in cell C5.
Sub LastDayOfCurrentMonth()
    Dim LDay As Double
    LDay = Application.WorksheetFunction.EoMonth(Range("C5"), "0")
    Range("D5") = VBA.Format(LDay, "mm/dd/yyyy")
End Sub

Run a VBA Code to Count the Last Day of the Month in Excel

Code Explanation:
  • In this code, we put 0 as the LDay to get the last day of the current month.
  • We took a variable to hold the output of theWorksheetFunction.EoMonth(Range(“C5”), “0”) which is a date in the form of a serial no. That’s why we used the VBA Format function to set our desired date format ( here, “dd/mm/yyyy”).
  • Now press F5 to run the code to get the output.

  • Hence, you will get 31-08-21 as the last day of the corresponding date.

Run a VBA Code to Count the Last Day of the Month in Excel

Related Content: How to Get Last Day of Previous Month in Excel


Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to count the first day of the month with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

2 Comments
  1. 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

      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

      1

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

      2

      Best Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo