## 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**.

- Go to

**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**

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

Mondaysfor a month of a year• Then, type the function name and enter the

month nameandyearto countMondays.As a result, we are getting

5which representsMondaysofJanuary 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