How to Round Off Date to the Nearest Month in Excel

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.

Precondition:

Create a list contains some dates.

How to Round Off Date to the Nearest Month 1

Method: 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.

How to Round Off Date to the Nearest Month 2

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

How to Round Off Date to the Nearest Month 3

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

How to Round Off Date to the Nearest Month 4

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)).

 

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)…
Related Posts

BMI Calculation Formula In Ms Excel

You might have come across a task in which you were assigned to make BMI calculations of the supplied numbers, and you may be looking for an efficient approach to accomplish this process rather than doing BMI calculations manually, by ...

Calculate The Period of Loan or Investment in Excel

In our daily life, most of us will invest or take a loan, so we need to master some simple financial functions to calculate the period needed for loans or investments. If you work as an accountant, you need to ...

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

Add Months To Date In Excel
Add Months To Date1

It is important to adjust time periods when performing financial modeling. In Excel, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months you ...

Add Days to Date in MS Excel
add days to date1

If you are an vavid MS Excel user, then you might have come across situations where you need to add the same or different days into the particular date, and if you have done this task manually, then let me ...

Filter Data By Date Field
filter by date field1

The use of Microsoft Excel's Format Cells and Auto Filter option is a great way to filter data by year or Month. This article will discuss how to filter by date field using Filter function and Format cells in Microsoft ...

How to Sum Data by Month Ignore Year in Excel

Sometimes we may want to sum data by month but ignoring the year. For example, if we want to investigate which month has the top sales among several years, we may sum data by month but ignoring the year information. ...

How to Sum Values Based on Month and Year in Excel
Sum Values Based on Month 6

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product ...

How to Convert Date & Time Format to Date in Excel
Convert Date & Time Format to Date 7

Sometimes we want to convert date and time format to date only format in excel for example convert 01/29/2019 06:51:03 to 01/29/2019, we can convert format by Formula or Format Settings. The two ways are easy to learn, so you ...

How to Delete or Remove Year from a Date in Excel
delete year from date2

This post will guide you how to delete or remove year from a given standard date in Excel. How do I remove the year from a date with a formula in Excel. Remove Year from a Date Assuming that you ...

Sidebar