In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Date Formulas

tlewis
7 - Meteor

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")) 

3 REPLIES 3
Thableaus
17 - Castor
17 - Castor

Hi @tlewis 

 

Would something like this work?

 

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

 

Cheers,

tlewis
7 - Meteor

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
17 - Castor
17 - Castor

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

Labels
Top Solution Authors