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

How To Hide Every Other Row in Excel (Using VBA)
hide every other row1

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

How to Sort Data but Keep Blank Cells in Excel

In daily work, if we sort data with blank cells included in the same column, these blank cells are listed at the bottom automatically after sorting. If we want to keep the positions of these blank cells unchanged and only ...

How to Copy and Paste Only Values and Ignore Formula

When we copy a cell applied with a formula, we copy the formula of the cell rather than copy the value showing in the cell. In this article we will introduce you the way to copy only value ignoring applied ...

How to Sort Date by Day of Week in Excel

Except sort data by “A to Z” (alphabetical order, for numbers from small to large), we can also sort data by date, month or year if these conditions are given. In this article, we will show you the way to ...

How to Select All Non-Blank Cells of a Range

In daily work, we may meet the cases that select all blank cells or non-blank cells of a range. You may know the way to select all blank cells as they are “blanks”. But for non-blank cells, they may contain ...

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