Date Time Diff formula not working all the time

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!

Date Time

Accepted answers

AngelosPachis

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

example.yxmd

All comments

AngelosPachis

Hi @CherieACI ,

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

example.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703