It is not good that your Excel spreadsheet has duff values in. If you can fix the source it will be better all round.
You should be able to deal with what you have here though, with a bit of code something like:
Date(if(Alt(DateField, 0) * 1 <> 0,
Date#(DateField, 'D/MM/YYYY h:mm:ss TT')),
'MM/DD/YYYY') as CorrectDate,
The code with the Alt and the multiplication is a sure fire way to check you have a valid date field in Excel (don't worry about how it looks in Excel - this is not important), if it is a valid date you can just take it as is. The Date# code is converting from string to a proper date, the parameter is the format of the string we are reading. The IF block is wrapped in a Date statement which then formats the field how we want it.
I know it's not very wise to use different formats, but the data comes from a machine running with a MM-DD-YYYY - format as default and my system uses a DD-MM-YYYY as default format. I have +100k of records, so I'm not eager to change the Excel-sheets.
The script of Clever works partly (the first 12 days of the month aren't recognized als shown in the pic).
It seems I'm not able to post an Excel sample. How can I do that?
I have had issues with dates in excel files when converting from mm/dd/yyyy to the correct way of dd/mm/yyyy. Excel likes to take a guess at what you mean and makes changes as it sees fit, so if it sees a date with the day of 13 or more it knows that is the day and switches but if the day is 12 or less it assumes it's the month and keeps it. No messages or warnings just "fixes" it.
To fix the dates in excel prior to loading into Qlikview would be my tactic. I use a macro that sets the formula of a cell to the cell value. This by default gets excel to treat the dates as mm/dd/yyyy. I tried it with your sample data and all of your dates were treated as October. It doesn't seem right to post excel macros on a QlikView post. So if such things offend, please look away
VBA Code block--
' Ron Campbell
'Highlight a section of cells
'step through cell by cell trimming the contents
'Trimming will only occur on visible cells, the macro skips any rows that are hidden or not visible as a result of a filter