Hi all, I have too many date time format in one single column. Eg. Feb.25 Apr. 30 yyyy-mm-dd dd-m-yyyy d-m-yyyy m/dd/yyyy Any idea to quick solve this? Thank in advance.

[image] Kenyx: That should be year 2019, as there are three people edit the excel file from 2019 to 2020, there are different formation due to lack of standard. There won’t be d/m/y and m/d/y, the reason of d-m is just omit the "0"s at the beginning of days and months Hi @Kenyx , based on your original post, as @bruno29a has mentioned, you showed both d-m-yyyy and m/d/yyyy formats, so I’m going to make the assumption that if the date uses / separators, then it is m d yyyy, but if it uses - separators then the day comes first. The attached flow will handle all the date formats you have given so far, and can be extended (or changed) to handle others by modifying the Rule Engine. e.g. [image] where as per your post, 1-8-2021 is 1 August 2021, whereas 1/10/2020 is January 10 2020 (because it uses / symbols)! :wink: So the dates are translated as follows: [image] [image] It uses a rule engine to define the date format to be used based on the pattern presented [image] Where there is no 4-digit sequence found in the date, it assumes there is no year and appends 2019 to the end of the datevalue string The rule engine identifies the date format mask to be applied for conversion. single and double digit days and months are handled automatically by simply specifying d/M or M/d in the format masks as appropriate. Because the String to Date&Time format mask is either configured, or dynamically supplied by a flow variable, in order to use a different mask for each row, it must be processed inside a loop that creates a different value in the flow variable for each iteration. An alternative is to use a Java Snippet to perform the same task. Within the Java Snippet, the format mask can be supplied via a column value. This would be noticeably faster on large data sets. The attached flow provides both the loop and a Java Snippet variant using the same input data. KNIME_Date_Handler.knwf (39.6 KB)

How to manipulate different date time from string format

KNIME Analytics Platform

Kenyx January 17, 2022, 8:10am 4

The bottom stream is finished, I am going to deal with yyyy-mm-dd and dd-mm-yyyy at the top part.
Without this dealing, the auto guess is unable to process.

This previous topics may help