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))
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?
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.
Is this better? Format the cell(s) with the formula as General.
=DATEDIF(C11,D11,"m")+((D11-EDATE(C11,DATEDIF(C11,D11,"m")))>15)
- Plugged in some more numbers and it looks good. Many thanks.
I derived a more simple formula which seems to get the same result:
=DATEDIF(C11,D11,"d")/30.437
Where the day count between the two dates is divided by the average number of days in a month. You can then round by simply adjusting the decimal places.
