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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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