Converting Dates with Multiple Formats in the same column

Question

Hi All,

Is there a way to convert a column of dates where they are in multiple formats in the same column?  Right now am using a date time tool to change the format on dates that are formatted as dd-Mon-yyyy, but in the same column I have dates formatted as dd-MM-yyyy.  It seems as if the tool is removing the dates formatted as 10-01-2021 since I chose the incoming as dd-Mon.-yy.  Also there are cells that will be blank in the incoming file.  Is there a way to convert these columns or do I need to fix this in Excel prior to importing the file?  See below for an example of what I am trying to convert, however there may be as many as four columns which will need to be converted.

L/C Orig Effective DateTPS Modifcation Date30-Jul-201528-Jun-202127-08-201810-oct-202018-05-2019  08-10-2021 30-07-202113-Oct-202113-Oct-202102-03-2020   22-Jun-202128-Jul-202122-Jun-202128-Jul-202113-Oct-202025-May-2021

MattR79 · Answer

See the attached for the raw data I am trying to convert.  From the image it appears that the WF seems to think that somewhere the year is first, yet I can't find that.  I pared the columns down to 1 to try and isolate the error and I don't see the year leading anywhere.  Thanks for your help.

Date Conversion Error.PNG

Date Conversion Fields.xlsx

atcodedog05 · Answer

Hi @MattR79

You need to set the format of the input date format giving parse function info of where is day, month, year. Refer highlighted below.

Provide me the complete sample with available date formats I can build a solution for you.

Hope this helps : )

MattR79 · Answer

I have tried that, however I am still getting conversion errors as shown below.  Unsure as to what I am doing wrong here.

Date Conversion Error.PNG