Contents:
Formula
= EOMONTH(date, (DAY(date) > DAY(EOMONTH(date, 0)) / 2) - 1) + 1By feeding any date into this formula either by cell reference or a hardcoded value, this formula will round the given date up or down to the first of the nearest month.
Explanation
Let's break the formula down into separate parts, using the example below:
In order to correctly calculate if the the input date should be rounded up or down, there are a few steps we need to take.
First, we use the DAY function to pull out the day. In this example 5.
We also need to know how many days are in the current month. To do that, we use DAY and EOMONTH function. EOMONTH returns the last day in the month from our date. In this case 4/30/2022. We then use DAY to pull out the 30 from that date, and divide by 2 to get the cutoff day between rounding up or down.
Since 30 / 2 = 15, and our input date is 5, our date is less than 15 and we need to round down. If the date is greater than 15, we round up.
Everything between the red parentheses returns a TRUE or FALSE value, and because TRUE = 1 and FALSE = 0, we subtract 1 from this value to give us the month argument for the EOMONTH function. A TRUE will return the last day in the current month (to round up) and a FALSE will return the last day in the previous month (to round down).
All of this, gives us the day before the first of the nearest month, and so we just need to add one on to the formula at the very end to give us the very first of the nearest month.
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, (DAY(date) > DAY(EOMONTH(date, 0)) / 2) - 1) + 1)Using 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 Nearest 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 nearest month.
By putting our formula:
= EOMONTH(B3, (DAY(B3) > DAY(EOMONTH(B3, 0)) / 2) - 1) + 1Next to the dates in column C, we can quickly calculate and round the dates. This process can be repeated anywhere in your workbook.
