Contents:
Formula
= EOMONTH(date, -2) + 1By feeding any date into this formula either by cell reference or a hardcoded value, this formula will round the given date down to the first of the previous month.
Explanation
Let's break the formula using the example below:
The EOMONTH function is used to return the end of the month from any month.
The first argument of the EOMONTH function is the start date, in this case we're using the date that we want to round down to the first of the previous month.
The second argument is the number of months to increment up or down by. By using "-2" in the formula, we are returning the end of the month, 2 months previous from the input date.
In this example, the EOMONTH function will return 1/31/2023, from there we simply add one additional day to that date to return the first of the next month, or 2/1/2023.
Note:
- This formula will work correctly with all months, short or long. Even months such as February will automatically correct and adjust for leap years.
- If the dates that you're working with have a timestamp associated with them, but they are not needed, we can strip them out by adding on the INT function like so:
= INT(EOMONTH(date, -2) + 1Using INT will round the date to the nearest whole number, removing the time data (stored as decimals) in Excel's date keeping conventions.
Example
How to Round a Date to the First of the Previous Month
In this example, we have a list of dates in column B, and want to round all of those dates to the first of the previous month in column C.
By putting our formula:
= EOMONTH(B3, -2) + 1Next to the dates in column C, we can quickly calculate and round the dates. This process can be repeated anywhere in your workbook.
