Date Time Diff formula not working all the time

[CherieACI]

I have a formula DateTimeDiff([effectiveEndDate],'2021-03-31',"Month") that is working sometimes but not always. Someone else helped me with my workflow and I'm not very familiar with how date time diff works. You can see below in my sample that it works sometimes but not always. Most of the ones that aren't working are 1 month off. What do I need to do to fix this?  

Incorrect:

Correct:

Thank you in advance!

[AngelosPachis · Accepted solution]

Hi @CherieACI ,

The issue arises from when you End Date Month has less than 31 days. You can go around that by either using the following formula, that will count the days difference and then yield the months by dividing by 30

FLOOR(DateTimeDiff([End Date],"2021-03-31","days")/30)

or by trimming both your start and end dates to the first of each month and using the formula you are already using with month as the date part

Hope that makes sense in a way.

Cheers,

Angelos

EDIT : Alteryx weekly challenge #10 is a great practice on date calculations if you feel like you want to learn more on that

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-10-Date-Time-Calculations/td-p/36737

[CherieACI]

The first solution worked great! I didn't realize either that there were daily/weekly challenges so thank you for sharing that too. One more question, what does the "Floor" means in your formula? 

Thanks for your help, 

Cherie

[AngelosPachis]

@CherieACI so the floor function will always round your number down. So 3.03 will become 3, 3.73 will also become 3. It's the same as using the round function in that instance, so your expression can also be

Round(DateTimeDiff([END],"2021-03-31","days")/30,1)