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. =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.

  2. 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.

  3. 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

  4. 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

  5. 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

  6. 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)

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

  8. 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.

  9. 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)

  10. 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))

  11. DEAR SIR,

    PLEASE HELP REGARDING THIS FORMULA IN EXCEL

    17/7/2007 17/7/2012 =DATEDIF(A,B, “D”) IF I GIVE THIS FORMULA WHY DONT GIVES RESULT WHAT IS THE PROBLEM
    REGARDS
    ANANTHA

  12. Please let me know that if i want to round off years what i should have to do for example when i use datedif formula it sometime shows like 57 Years, 11 Months & 30 Days but i want this like 58 years, 0 Month & 0 days
    Please let me know if anybody knows the solution.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.