Month DateDiff round down to nearest month

[Anonymous]

Hi Everyone,

When I'm using the DateDiff Function to get the difference between two dates it works fine, however it rounds up my dates and I would like to round this down. E.g if the difference in months is 16 months and 16 days, it rounds it up to 17 months . However I would like to round down to 16. Similarly if the difference is 23 days, I would like it to round down to 0 month as opposed to 1 etc. I have tried using INT(DateDiff(XXX) however the issue is still the same.

Thank you in advance

P

[v-jiascu-msft]

Hi @Anonymous,

How did you create the formula? It seems right to your needs. Please refer to the snapshot below.

Best Regards,
Dale

[rafaelmpsantos · Accepted solution]

Hi Datediff in always ROUNDUP, for Always rounddown you may use -1 like this:

diference = DATEDIFF(Table1[Column1];Table1[Column2];MONTH)-1

[pabloluque14]

This is not acceptable solution.  Subtracting one will create problems for you in other situations

[wbeck]

Correct, the solution is not to subtract 1. Any ideas what the solution is? I'm trying to do a calculation for Years of Service. Some of the values are getting rounded up (i.e., 5 or more months).

[Anonymous]

Thank you 🤗🤗