Sometimes we may get some dates in excel and we want to calculate their nearest month based on date. Actually, this can be implemented by applying formula in excel. This tutorial will introduce you the formula to round off date to its nearest month in Excel 2013/2016/2019/365.

Rounding dates to the nearest month can help simplify data analysis by grouping dates into larger time periods.

Precondition:

Create a list contains some dates.

1. Round Off Date to the Nearest Month by Formula

Step 1: In B2 enter the formula:

 =MONTH(EOMONTH(A2,(DAY(A2)>15)+0)).

In this formula, EOMONTH function returns the date of the last day of a month before or after the specified date; MONTH function returns an integer between 1 and 12 which represents the month in a year. Use the combination of above two functions can round off date to its nearest month.

Step 2: Press Enter to get returned value. Verify that for date 1/4/2020, the nearest month is January.

Step 3: Drag down the fill handle till reaching the end of the list. Verify that all the nearest months are calculated properly.

Comment:

1. This formula only returns an approximate value, no matter how many days a month, the nearest month for date >15 is the next month.
2. For special month like February, you can adjust the formula to match your request, for example if you want to get 3 (March) for date>13, you can update your formula: =MONTH(EOMONTH(A4,(DAY(A4)>13)+0)).

Now Let’s break down the formula step by step:

In this formula, the EOMONTH function calculates the end of the month based on a given date.

In this case, A2 represents the input date.

(DAY(A2) greater than 15) checks if the day of the date is greater than 15. If the condition is true, it returns TRUE (which is equivalent to 1), and if false, it returns FALSE (which is equivalent to 0).

The (DAY(A1) greater than 15) + 0 part adds 0 to the logical value, which effectively converts the TRUE or FALSE result to 1 or 0, respectively.

So, if the day is greater than 15, it adds 1 to the original date, and if the day is 15 or earlier, it keeps the original date.

2. Video: Round Off Date to the Nearest Month

This video will show you rounding dates by formula using Excel built-in MONTH, EOMONTH, and DAY functions in Excel.

3. Related Functions

• Excel MONTH function
  The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
• Excel DAY function
  The Excel DAY function returns a day of a date (from 1 to 31).The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)…