Most of the times, we don’t get clean data. Especially the data from web or non-transactional systems where there is no data integrity or constraints, which will have some noise. Let’s assume that we have single date field which has multiple date formats (As shown below).
Currently, “MyDateField” field has 4 different date formats, and out of which 3 of them are not date fields (which are aligned as strings on the left hand side). There is only one correct date format with two rows.
There are many ways to convert the above string values in to TRUE date format. You can write multiple nested IF statements to evaluate the date formatting. Instead we can use the Alt() function which returns the first parameter while checking for the valid number representation. Alt() function will test if the field “MyDateField” contains a date according to any of the specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text ‘No Match’ will be returned (without any valid number representation).
,Date#(MyDateField,’DD/MMM/YYYY’))) AS MyFieldInDateFormat
Using the above script you can convert the dates into correct format. See the below example where “MyDateField“ is old field and “MyFieldInDateFormat” is new field with correct date format.