Date Conversion Error

SH_94

Dear Community,

I encountered the conversion error on the date format which consist of following date format: 

Error in Alteryx 

Below is the format used to convert the date format accordingly.

Formula in the text format :

IF !IsNull(DateTimeParse([Invoice Date],"%d/%m/%y")) 

// Check whether it falls in the date format

 

THEN DateTimeParse([Invoice Date],"%d/%m/%y") 

// If yes then parse it with a new format

 

ELSEIF 

!IsNull(DateTimeParse([Invoice Date],"%d-%b-%y")) THEN DateTimeParse([Invoice Date],"%d-%b-%y")

 

ELSEIF 

!IsNull(DateTimeParse([Invoice Date],"%Y-%m-%d")) THEN DateTimeParse([Invoice Date],"%Y-%m-%d")

 

ELSEIF 

!IsNull(DateTimeParse([Invoice Date],"%Y-%m-%d")) THEN ToDate(DateTimeParse([Invoice Date],"%Y-%m-%d"))

 

ELSE Null() ENDIF // Returns null if its a new format

Could anyone enlighten me on which part that i have made the error and how to correct it?

Many thanks in advance

Date Format.xlsx

apathetichell

I think you are trying to convert across multiple types of dates in the same column - but your datetimeparse() optionality is just creating errors. The true/false checking shouldn't use datetimeparse since it's not creating a null per se -it's creating an error. The better way to do this is with a regex_match function - and I'll upload a workflow to show you how to do this in a few minutes...

Ignore the yucky error messages... I think it goes through the values for each part of the switch but only runs the relevant section... regex wasn't going to work because it can't tell the difference between 19/11/26 and 11/26/19  - at least not easily...

Notes - your integer is off by two days according to Alteryx. I cut down your format so the switch was clearer.

all the date conversion.yxmd