calculating months between two dates with rounding

[qdanazumi]

I have been trying to look for the number between these two months while rounding to the next month if the day passes the 15th

[#2]

@qdanazumi 

Calculate the difference between two dates

Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in a cell, and an end date in another. Then type a formula like one of the following.

You can download an example workbook with all of the examples in this article.

You can follow along, or create your own formulas.

[#3]

@NikolinoDE what if I wanted after the 15 of each the month to round it to the next month

[#4]

@NikolinoDE The formula didn't end up working, unfortunately. 

07/01/22

08/20/23

I know that because 8/31/23 is past the 15th of the month I would round it to the next month so I'm pretty much looking for the total number of months between 7/1/22 and 9/1/23. I know the total number of months is 14. What formula should I use to figure that out?

[#5 · Accepted solution]

@qdanazumi 

With dates in A3 and B3:

=(12*YEAR(B3)+MONTH(B3)+(DAY(B3)>15))-(12*YEAR(A3)+MONTH(A3)+(DAY(A3)>15))

[#6]

I tried this formula and unfortunately, it didn't work. The formula @hans gave seems to work

[#7]

how would I change the format of the cell

[#8]

Select cell or range of cells
right mousekey
Select "Format Cells...".
Numbers -> Set to "Default".

[#9]

@NikolinoDE 

Hi Niko,

Your formula doesn't work correctly if the dates are more than a year apart, nor if the month number of the first date is larger than the month number of the second date:

[#10]

That's right, you're absolutely right...strange...I have to find out why this is happening.

Qdanazumi: Please use Mr. Hans Vogelaar's formula and ignore mine.
sry for the circumstances.

[#11]

thank you

[#12]

@NikolinoDE 

It's because you subtract two month numbers without taking the year into account, so the result will vary from 1-12 = -11 to 12-1 = 11.