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)?
Solved! Go to 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
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
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!