Date Formulas
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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"))
Solved! Go to Solution.
- Labels:
- Date Time
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @tlewis
Would something like this work?
DateTimeAdd(AnnivDate, 2014 - DateTimeYear(AnnivDate), "years")
Cheers,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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,
