date

Question

Hi Anyone,

i'm looking for a way to show dates that are maturing within 12 months of the asofdate, per below. If it is maturing i need it to say Yes if not No.

AsofdateMaturity DateMaturing in 12 months5/31/20239/30/2023Yes5/31/20239/30/2025No5/31/20236/30/2023Yes

Let me know if you have questions.

Thanks,

ed_hayter · Answer

From the Documentation on DateTimeDiff if you want to know how Datediff works, skip if you just want your answer:

DateTimeDiff("2016-02-15 00:00:00", "2016-01-15 00:00:01", "Months") returns 1 (because the start and end are the same day of the month).

DateTimeDiff("2012-02-29","2011-03-01","years") returns 0 (even though 2012-02-29 is 365 days after 2011-03-01, February 29 is before March 1st, so "one year" has not yet been completed).

DateTimeDiff("2016-02-14", "2016-01-15", "Months") returns 0 (because the day in February is less than the day in January).

DateTimeDiff("2016-02-14 23:59:59", "2016-01-15 00:00:00", "Months") returns 0 (even though it is only one second short of reaching the required day).

DateTimeDiff('2017-02-28', '2016-02-29', 'Months') returns 11 (even though the 28th is the last day of February in 2017, the 28 is less than 29).

DateTimeDiff('2017-02-28 00:59:59.28139502', '2017-02-28 00:59:59.12383125', 'msecs') returns 157 (for milliseconds between date-time values)

from https://help.alteryx.com/20231/designer/datetime-functions

Basically it only returns an integer so if a month is not completed then it rounds down to the nearest completed month June 30 is recognized as just short of a month completed so is till 0 so when the next year comes around it is still rounding down to 12 months rather than ticking over to 13.

I think if you switch the date diff to 1 year rather than 12 months you can get it to cutoff at 5/31/2024

See the updated flow below

Datediff.yxmd

ntudev · Answer

i thought 12 month should end of 5/31/2024 but this formula goes to 06/30/2024, why is that? how do i make it go to end of 5/31/2024?

binu_acs · Answer

@ntudev Another method

Date_060723.yxmd