Syncing the Format of Various Date Fields
- 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 trying to make all of my date fields uniform. Most of the date fields are based off of a calculation, but depending on the value, the calculation did not apply, leaving me with two formats in one field (i.e 2016-01-01 and 01/01/16). Additionally, a couple of the date fields are not calculated and are the alteryx default date format. Where this is causing me issues is when I am trying to then do calculations off of these various dates and getting Null values.
Can anyone help me with getting these all to be the same format. DateTimeFormat has not worked for me. I am guessing because of the inconsistency of the dates.
Thanks in advance.
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I recommend having a look at this excellent macro (and even better blog post) written by @JPKa ...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I would suggest a formula tool using a Regex_match to determine the format.
IIF(REGEX_Match([Field1],"\d{2}/\d{2}/\d{2}"),DateTimeParse([Field1],"%m/%d/%y"),[Field1])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for the formula. Looks like everything is working for me now. Not sure if there was any better way to do it, but I put that formula into the tool, once for each way the dates were coming in and then a final formatting formula at the end of it.
