Rounding DATEDIF

DATEDIF is a worksheet function that will return the difference between two dates in a variety of intervals. As far as I can tell, DATEDIF truncates any partial intervals. If the difference was two months and one day, it would return two months. I had a situation where I needed to count partial months as whole months. I didn’t see any options for that using DATEDIF, so I came up with this formula:

`=DATEDIF(A1,B1,"m")+IF(DAY(A1)=DAY(B1),0,1)`

The idea is that it will always add one unless the start date and end date are the same day, and thus there is no fraction of a month. This formula happened to work for my data, but there’s an error. If the start date is 28 Feb 2005 and the end date is 31 Mar 2005, it returns 2, which is obviously wrong. Then I came up with this:

`=DATEDIF(A1,B1,"m")+IF(DAY(A1+1)=DAY(B1+1),0,1)`

That takes care of two month end dates that aren’t the same number, but still gives wrong results for, say, 28 Feb v. 30 Mar. This should not be that hard, I think I just killed too many brain cells over the holiday weekend. Suggestions?

16 thoughts on “Rounding DATEDIF”

1. Brett says:

=DATEDIF(EOMONTH(I656,0),EOMONTH(J656,0),”m”)

Maybe?

2. Brett says:

=DATEDIF(EOMONTH(I656,0),EOMONTH(J656,0),”m”)+IF(AND(EOMONTH(I656,0)=EOMONTH(J656,0),DATEDIF(I656,J656,”d”)>0),1,0)

if you need partial month in same month counted as one.

3. frank says:

>

Dick, doesn’t =MONTH(A1)-MONTH(B1) does that?

4. Brett: I can’t be sure end user has the ATP so EOMONTH is out.

Frank: Okay as long as they are in the same year, but that’s not the case for any of the data I was working with.

5. DA says:

DK:

As a non-tech guy, I cannot understand why you would want to calculate these dates. I guess I’ll just observe the commentary to determine whether an adequate formula exists for this calculation.

DA

6. Paul says:

DATE(YEAR(a1),MONTH(a1)+1,0)

7. Jazzer says:

Hi Dick,

This worked for my test material. Not sure if I understood the problem corretly though:

=DATEDIF(0,B1,”m”)-DATEDIF(0,A1,”m”)+(DAY(A1)DAY(B1))-(MONTH(DATE(YEAR(A1),MONTH(A1),DAY(A1)+1))MONTH(A1))

– Asser

8. Jazzer says:

ok, “not equal” sign didn’t display. So here it is replaced with NEQ

=DATEDIF(0,B1,”m”)-DATEDIF(0,A1,”m”)+(DAY(A1) NEQ DAY(B1))-(MONTH(DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)) NEQ MONTH(A1))

– Asser

9. Helen says:

Maybe I’m missing some complication here, but why not just change the “equals” sign in your original formula to “less than or equal to”?
=DATEDIF(A1,B1,”m”)+IF(DAY(A1)<=DAY(B1),0,1)

10. Helen says:

Ignore my post above, I need more sleep. And this blog needs an “edit my comment” feature! :)

11. I needed to calculate amoritization counting every month as a whole month regardless of the date of the month the transcation begins and ends. Brets post sounded the most promising.
=DATEDIF(EOMONTH(I656,0),EOMONTH(J656,0),îmî)+IF(AND(EOMONTH(I656,0)=EOMONTH(J656,0),DATEDIF(I656,J656,îdî)>0),1,0)

I subsitiute a cell reference for the date – ie EOMONTH(g20,0)as above. Don’t see any use for the IF statement added to the end.

12. I was looking for a solution to calculate # of months between 2 dates and run into this posting. Since I don’t have the DatedIF function, I use the following workaround and it seems to work well:
=ROUND((EOMONTH(E21,0)-EOMONTH(D21,0))/30,0)

13. AJ says:

All:

I stumbled upon this page while I was researching the initial post by DK. Not sure if this is still of help for you, but just so you know – I used the following function, which seems to work fine.

=(YEAR(B9)-YEAR(A9))*12+(MONTH(B9)-MONTH(A9))

14. DEAR SIR,