Date and time interpretation

Qlik Sense stores each date, time, and timestamp found in data as a date serial number. The date serial number is used for dates, times and timestamps and in arithmetic calculations based on date and time entities. Dates and times can thus be added and subtracted, intervals can be compared, and so on.

The date serial number is the (real valued) number of days passed since December 30, 1899, that is, the Qlik Sense format is identical to the 1900 date system used by Microsoft Excel and other programs, in the range between March 1, 1900 and February 28, 2100. For example, 33857 corresponds to September 10, 1992. Outside this range, Qlik Sense uses the same date system extended to the Gregorian calendar.

The serial number for times is a number between 0 and 1. The serial number 0.00000 corresponds to 00:00:00, whereas 0.99999 corresponds to 23:59:59. Mixed numbers indicate the date and time: the serial number 2.5 represents January 1, 1900 at 12:00 noon.

The data is, however, displayed according to the format of the string. By default, the settings made in the Control Panel are used. It is also possible to set the format of the data by using the number interpretation variables in the script or with the help of a formatting function. Lastly, it is also possible to reformat the data in the properties sheet of the sheet object.

To learn more about number interpretation variables, see Number interpretation variables.

To learn more about formatting functions, see Formatting functions.

Example 1:  

• 1997-08-06 is stored as 35648
• 09:00 is stored as 0.375
• 1997-08-06 09:00 is stored as 35648.375

and the other way around:

• 35648 with number format 'D/M/YY' is shown as 6/8/97
• 0.375 with number format 'hh.mm' is shown as 09.00

Qlik Sense follows a set of rules to try to interpret dates, times, and other data types. The final result, however, will be affected by a number of factors as described here.

Example 2:  

These examples assume the following default settings:

• Number decimal separator:
• Short date format: YY-MM-DD
• Time format: hh:mm

The following table shows the different representations when data is read into Qlik Sense without the special interpretation function in the script:

The following table shows the different representations when data is read into Qlik Sense using the date#( A, 'M/D/YY') interpretation function in the script: