Date format issue when saving Qlik Table to Excel file from DataLoad Editor

[devrajR]

I am moving my report from QLikView to QlikSense. All the code is same. I am reading the data from SQL and saving into a QlikTable and then storing into a excel file from the data load editor only (not from sheets). But the date format exported into excel from QlikSense is different to the one generated from QlikView.

So, I changed the formatting of the datetime column to:

Date([TxDateTime],'DD-MM-YYYY hh:mm:ss') as [Tx_datetime_format]

The strange part is, when I keep the column name in QlikSense same as it is in QlikView (Tx_datetime_format) the date that is exported to excel is showing as 26:43.7.

When I change the column name in QlikSense to "Tx_datetime format" or any different column name, the date that is exported to excel shows as 2023-01-26 10:26:44, which is as expected. The Output from QlikView is also saved as 2023-01-26 10:26:44

So, why does changing column name gives the correct output but keeping the same column name gives an output in different format? I am not able to understand whether this issue is in QlikSense or Excel? 

I can change the column name, but the report is used by other teams and they also will have to make changes at their end which is not ideal.

[marcus_sommer]

Date() is not really suitable to format a timestamp. Better would be use timestamp(). Before this make sure that your date and time fields are really properly recognized because even by an identically script (is it really absolutely the same ?) the different environment could have an impact.

Therefore load these fields also with num(Field) to see the numeric value behind the formatting - and if it returned '-' respectively NULL the field-values aren't treated as number else as strings.