When getting data from Excel source, the date format is assumed when the format of the column is set to Any (early stage of data preparation, see printscreen below) and the header of the column is "Date".
Something system wise happening during header promotion. When we want to replace incorrect values (in this case 00.00.0000 generated for empty date by SAP) by null or another number, it returns an error stating 00.00.0000 is not a date even so the column type is set to any.
Enter a Date Value message
Could the logic be fixed so when the column type is set to any, we are not obliged to add the step before the header promotion generated automatically by the system ?