Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
francar0815
Contributor
Contributor

Excel timestamp import

Hi experts,

 

I'm stuck getting an excel timestamp imported.

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

francar0815_0-1649943103532.png

Timestamp number looks like this in excel:

francar0815_1-1649943216576.png

 

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

francar0815_2-1649943295592.png

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

The imported timestamp number looks like this in qlik:

francar0815_3-1649943430168.png

 

 

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

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

2 Replies
marcus_sommer

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
Contributor
Contributor
Author

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

francar0815_0-1650631013814.png