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?
- 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:
- 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.
- how would I change the format of the cell
- Select cell or range of cells
right mousekey
Select "Format Cells...".
Numbers -> Set to "Default".
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)
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.
