How to Get the First Day Of The Month In Excel?

Sometimes, you may want to get the first day of the month when you already have a date.

For example, if you have 15 Jan 2023, you can use a simple formula to give you the first day of the month, which would be 01 Jan 2023.

I have seen people use this in project planning, where they have the due dates for specific tasks, and they want to get the first day of the month for every given date.

This helps them plan ahead and get clarity on how many days remain for each task when starting from the first day of the month.

In this tutorial, we will go over the three main methods to get the first day of the month when given we have a date in Microsoft Excel.

How to Get the First Day of the Month using the DAY Function

DAY function takes a date and gives us the day number in that month.  For example, if I have a date (say 15 Jan, 2023) in cell A1, and I use the formula =DAY(A1) in some other cell, it will give me 15, where 15 is the day value in the date.

Now let me show you how we can use the day function to get the first day of the month.

Below I have a date in cell A2 and I want to get the first day of the month in cell B2.

Below is the formula that will give me the first day of the month for this given date:

=A2 - DAY(A2) + 1

Let me explain how this formula works.

For the first part of the formula, we have the cell reference A2 which denotes the date 11 February 2018. The DAY Function on its own is used to return the day of a date. 

The syntax of the DAY Function is:

=DAY(serial_number)

• serial_number – this is a date. You can use a cell reference containing a date, or enter the date directly into the function using the DATE function. This is a required value.

Therefore as part of this formula, the DAY Function will give us 11, since it takes the date in A2, and gives just the day, which is 11. 

So, when taking the first part of the formula into account, we then have 11 February 2018 – 11. This will give us 31 January 2018. The addition of 1, to this date, then gives us 1 February 2018. This is our desired answer.

Also read: How to Get Total Days in Month in Excel?

How to Get the First Day of the Month using the EOMONTH Function

There is another way to calculate the first day of the month, from a given date and that is by using the EOMONTH Function in a formula.

Below I have a date in cell A2 and I want to get the first day of the month in cell B2.

The below EOMONTH formula will give us the result

=EOMONTH(A2, -1) + 1

The EOMONTH Function on its own returns the serial number for the last day of the month, based on a specified number of months before or after the start date. 

The syntax of the EOMONTH Function is:

=EOMONTH(start_date, months)

• start_date – this refers to a given start_date. This is a required value.
• months – this refers to the number of months before or after the start_date. This is an integer value that can be 0, positive or negative. This is a required value.
  • If it’s 0, the function returns the date of the last day of the month, in the same month as the start_date.
  • If it’s positive and greater than 0, the function will return the date of the last day of the month, for the number of months specified after the start_date.
  • If it’s negative and less than 0, the function will return the date of the last day of the month, for the number of months specified before the start_date.

In this formula, we use the EOMONTH function to return the last day of the previous month. We have specified it must be one month before the date in cell A2, by using -1.

Since the date in cell A2 is 25 August 2022, this would return 31 July 2022.

However, because we are adding 1 day to this value, it becomes the first day of August.

So, in this way, we get the first day of the month of August, which is 1 August 2022.

Also read: Find Last Monday of the Month Date in Excel (Easy Formula)

How to get the First Day of the Month using VBA

If you understood how we used the EOMONTH Function to get the first day of the month from the example above, then you should have no problem understanding the logic in this section.

We are going to access the EOMONTH worksheet function in our VBA code. 

We do this through the WorksheetFunction property of the Application object.

1. We have a date entered in cell A2.

2. Now, go to the Developer Tab, and in the Code Group, click Visual Basic.

3. Go to Insert and select Module.

4. Copy and paste the following code.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Getting_The_First_Day()
Dim result As Variant
For Each cell In Selection
result = Format(WorksheetFunction.EoMonth(cell, -1) + 1, "dd - mmm - yy")
cell.Offset(0, 1).Value = result
Next cell
End Sub

5. Select the cell that has the date (if you have dates in multiple cells in the column, select all the cells with the date)
6. Press F5 on your keyboard to run the code and you should see the following.

In this short tutorial, I showed you three simple ways you can use to quickly get the first day of the month when you already have a date in that month.

This can easily be done using the EOMONTH formula or the DAY formula.

And in case this is something you need to do quite often, then you can also use the VBA code where you can simply select a range of cells that contains the date, and then run the code and it will give you the first day of the month in the adjacent column.

Other articles you may also like:

• How to Highlight Dates Before Today in Excel?
• Calculate Days Between Two Dates in Excel (Workdays/Weekends)
• How to Calculate the Number of Months Between Two Dates in Excel?
• How to Change Date and Time to Date in Excel (4 Easy Ways)
• How to Apply Short Date Format in Excel? 3 Easy Ways!
• Using IF Function with Dates in Excel (Easy Examples)
• Why are Dates Shown as Hashtags in Excel? Easy Fix!
• How to Convert Date to Day of Week in Excel (3 Easy Ways)