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]

Hi @tlewis 

Would something like this work?

DateTimeAdd(AnnivDate, 2014 - DateTimeYear(AnnivDate), "years") 

Cheers,

[tlewis]

Looks like this would return 2/28/2014, and not 3/1/2014.

DateTimeadd("2000-02-29", 2014 - DateTimeYear("2000-02-29"), "years")

I will try and work with something like this though.

[Thableaus · Accepted solution]

@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,