This post demonstrates how to find the nearest month from a date in excel.
We will base our calculation on 15 days i.e. we will find the nearest month from a date in excel by rounding off to the nearest month, so the month would be +1 from the current month if the date is greater than 15 and would be -1 if the date is less than 15.
The pic below shows us the nearest months in integer for the given dates in column A.
As you could above, we have calculated the nearest months from the date on 15 day basis, in row 3 the date is 17 and hence the nearest month would be June(6) whereas in row 5 the date is 14th and hence it shows the current month only as the nearest month.
We will see a combination of Month and Day formula to calculate the nearest month from a date in excel.
Enter the formula =MONTH(EOMONTH(A2,(DAY(A2)>15)+0)) in cell B2 to get the nearest month in excel.
The Excel EOMONTH function returns the last day of the month, x months in the past or future.
Drag the formula in column B till you have the data available in column A
As you could see we have got the nearest month from a date in excel.
Hope this helped.
