Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

validating date format

nogap2
7 - Meteor

Is there a way to validate the dates in a CSV file that feeds a flow for correct date formatting (formatting hasn't been lost in the file to show 43555 instead of 3/31/2019)?

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

@nogap2 ,

 

I'm a little bit confused by your post.  Typically, an Excel date serial number is the days since 01/01/1900.  For your date, the conversion appears to be 2 days earlier:

 

DateTimeAdd("1900-01-01",[Field1]-2,"days")

 

If you use a formula like this, then you can reformat numbers to dates using that formula.

 

IF Regex_Match([Field1],"\d{5}") 
   THEN DateTimeAdd("1900-01-01",[Field1]-2,"days")
ELSE
   [Field1]
ENDIF

That will change 5 digit numbers to a date.

 

You could go the other way too.

 

Cheers,


Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
nogap2
7 - Meteor

We are receiving a feed from an external source, and for some reason some months the date fields are formatted as DD/MM/YYYY and other months they lose their formatting and show as straight numbers.  When this happens the date conversions in our flow do nothing and the fields/calculations that run on date differentials just give us null fields. I'll give this a try and see if it will work.

 

 

Thanks!

Trish

nogap2
7 - Meteor

Thanks got me on the right track.  But the formula was a little off. This is what I used in the If statement:

    DateTimeAdd("1900-01-01", ToNumber([YOURFIELD])-2,"days")


Was just missing the ToNumber() in the formula.

 

Solved... thanks a million!

Labels