community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

validating date format

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

Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
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

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