This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
I am having the Period End Date as well as the Expiry date of certain contracts. I now want to calculate the difference between Expiry date and Period end date in Years. I have tried the following formula:
If the result is negative, this means the contract is overdue, if it is positive i want to group it into maturity buckets.
Everything is working as intended except for the leap years. Due to those, the calculation of DateTimeDiff/365 does often not give me the correct numbers. When calculating the time difference between 31/12/2020 and 31/12/2019 I would actually have to divide by 366 since there is an additional day in February.
Is there a way to design this flexibly, even for longer time periods? Please see my formulas and my time buckets down below:
I have already tried things like using 1.00247 instead of 1 in the AgeBucket calclulation, but that will of course falsify the buckets in a non-leap year.