How to Get the First Day of the Month From A Date in Excel

Here is a quick formula that will allow you to return the beginning of the month from any date in Excel.

Contents:

Formula

Explanation

Example - Get Beginning of Month from Date

Formula

= EOMONTH(date,-1) + 1

Simply replace the "date" with any input date, either hard coded value or a cell reference, to return the first day of the month in which that date is found.

Explanation

The EOMONTH function returns the last day of any month for a given date. This function also has the ability to return past or future months using its months argument.

In this example, the function is returning the end of the month of the date held in B2. The 0 is telling the function to return the end of the month that the date occurs in. A 1 would give the end of the next month, while a -1 would give the end of the previous month.

= EOMONTH(date,-1) + 1

In our original formula above, we are returning the last day of the month of the previous month with the EOMONTH function, and then adding 1.

By adding 1 onto the last day of the previous month, the beginning of the current month will always be returned.

Example

How to Round Date to the First of the Month Using a Formula

Assuming the dates are listed in column B, we can return the beginning of the month from any date by dropping in this formula along them.

= EOMONTH(B3,-1) + 1