Excel timestamp import

[francar0815]

Hi experts,

I'm stuck getting an excel timestamp imported.

Timestamp in excel is formatted like DD.MM.YYYY hh:mm:ss:

Timestamp number looks like this in excel:

If I try to import the data into Qlik via dataeditor, the timestamp looks like this:

It's the same row, just 4 years and 1 day earlier.

The imported timestamp number looks like this in qlik:

I searched the web for about 2 hours now but couldn't find a solution.

Did someone have the same issue and could solve it?

Excel format is XLS (97-2003)

Regards

Carsten

[marcus_sommer · Accepted solution]

It's caused from a special Excel date-setting called 1904 which moved the dates for 4 years. It was created to get a compatibility to another table-calculation and provides the capability to handle negative times. This setting isn't related to a document else it's a global user-setting and it's not recommended to use unless is very specific cases with a full awareness which side-effects come with it. AFAIK you couldn't change it easily because it will affect all your Excel-files. But within a mid-term you should consider it carefully.

To solve your issue now in Sense you need to add the 4 years manually to your dates.

- Marcus

[francar0815]

Hi Marcus,

Thanks for helping.

The excelsheets I got contain all data I need. There is no need to touch the excels.

So i followed your suggestion and added the missing 4 years and 1 day manually:

Date(AddYears("Datum/Zeit",4) + 1, 'DD.MM.YYYY HH:mm:ss') as Zeit