Unify Date Time format to output date

[45179902]

I've built a batch macro to read in multiple input files sharing the same layout and output data from these files to an Excel worksheet. So all the data from the input files will be written to this single worksheet.

The problem is that the input files are manually created by others. The dates are written in different formats and some of them are not recognisable in Alteryx. Some dates are written as 24/12/2021 or 12/24/2021 while others are written as 24-Dec-21. Because these dates are unrecognisable, Alteryx currently outputs NULLs instead of the dates to the Excel worksheet.

How to unify the date time format so that Alteryx can recognise these dates and output them successfully? I know there is a Date Time tool but these dates are in the same column and have different formats. So I'm not sure how to apply DateTime tool based on the format of incoming sources.

[binuacs · Accepted solution]

@45179902 

If IsNull(DateTimeParse([Dates],"%d/%m/%Y")) && IsNull(DateTimeParse([Dates],"%m/%d/%Y")) Then DateTimeParse([Dates],"%d-%b-%y")
ElseIf IsNull(DateTimeParse([Dates],"%d/%m/%Y")) Then
DateTimeParse([Dates],"%m/%d/%Y")
Else DateTimeParse([Dates],"%d/%m/%Y")
EndIf

Spoiler

[45179902]

@binuacs Thanks for your solution. This is helpful and close to what I'm looking for. However, I encountered some errors when using this solution: some other dates become NULLs. If I'm able to solve the problem, I will accept your response as the solution. 

[binuacs]

@45179902  this solution works only for the three format you mentioned. If you have different date format then you need to add those format in the If condition