Forum Discussion
calculating months between two dates with rounding
With dates in A3 and B3:
=(12*YEAR(B3)+MONTH(B3)+(DAY(B3)>15))-(12*YEAR(A3)+MONTH(A3)+(DAY(A3)>15))
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.
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?
- I tried this formula and unfortunately, it didn't work. The formula hans gave seems to work
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:
With dates in A3 and B3:
=(12*YEAR(B3)+MONTH(B3)+(DAY(B3)>15))-(12*YEAR(A3)+MONTH(A3)+(DAY(A3)>15))
I find this formula is not working at times, if the idea is to round, say, three months and 16 days to four months, or three months and 15 days to two months.
For instance, the range 24-05-12 to 24-07-24 (2m 13d) yields 3 months using this formula. The range 24-10-03 to 24-11-16 (1m 14d) yields 2 months, but then the range 25-08-01 to 25-09-15 (1m 15d) yields 1 month as it should. And very oddly, the range 24-03-15 to 25-06-18 (15m 4d) yields 16 months.
