Multiple IF statements using dates

[benmillea]

I'm trying to recreate a formula that I used to use in Excel that would append a "Recency" flag based on the difference between today's date and the date in my data. My formula looks like this:

IF(ROUNDDOWN(($N$1-M2)/7,0)<2,"(1)0-2 Weeks",
IF(ROUNDDOWN(($N$1-M2)/7,0)<6,"(2)2-6 Weeks",
IF(ROUNDDOWN(($N$1-M2)/7,0)<12,"(3)6-12 Weeks",
IF(ROUNDDOWN(($N$1-M2)/7,0)<26,"(4)12-26 Weeks",
"(5)26-52 Weeks")))))

Where $N$1 = today's date and M2 = the date in my data.

Does anyone know the best way of going about this in Alteryx please?

Thank you

[CharlieS · Accepted solution]

Sure thing. Since "Weeks" isn't a supported denomination in the DateTimeDiff function, here's what this could look like using days in Alteryx.

IF DateTimeDiff(DateTimeNow(),[Date],"days")<14 THEN "(1)0-2 Weeks"
ELSEIF DateTimeDiff(DateTimeNow(),[Date],"days")<42 THEN "(2)2-6 Weeks"
ELSEIF DateTimeDiff(DateTimeNow(),[Date],"days")<84 THEN "(3)6-12 Weeks"
ELSEIF DateTimeDiff(DateTimeNow(),[Date],"days")<182 THEN "(4)12-16 Weeks"
ELSE "(5)26-52 Weeks" ENDIF

[Jurjenz]

Hi benmillea,

I think you could create this by using a combination of the DateTimeDiff function in an IF statement in a formula tool.

IF Ceil(DateTimeDiff(DateTimeNow(), [yourdate], 'days')/7) < 3 THEN ''0-2 Weeks"

ELSEIF etc.

Hope this helps!

Regards,

[benmillea]

Superb! Thanks very much for that.