Date Formulas

tlewis

I am attempting to transfer a formula from an Access query into Alteryx. I have a formula that returns the day before a policy anniversary. I am having some trouble when the policy anniversary falls on a leap year (2/29).

In Access, my DateSerial function will automatically return "3/1" for non-leap year anniversaries. I cannot find a similar function in Alteryx. When I am using the DateTimeAdd, it appears that you cannot pass an invalid date (e.g. 2/29/2014 anniversary) in the first argument or it will return Null.

DateTimeAdd(("2014" + "-" +  PadLeft(ToString(DateTimeMonth([AnnivDate])), 2, "0") + "-" + PadLeft(ToString(DateTimeDay([AnnivDate])), 2, "0")),-1,"days")) 

Thableaus

@tlewis 

You could use something like this in the Formula Tool (in two steps):

1st box:

Field B:

DateTimeadd(FieldA, 2014 - DateTimeYear(FieldA), "years")

2nd box:

Field B:

IF DateTimeDay(FieldA) != DateTimeDay(FieldB)

THEN DateTimeAdd(FieldB, 1, "days")

ELSE FieldB ENDIF

See if this works well.

Cheers,