validating date format

[nogap2]

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

[MarqueeCrew · Accepted solution]

@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

[nogap2]

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]

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!