Date and time interpretation

QlikView 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 QlikView 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, QlikView 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.

Number interpretation variables

Formatting functions

Example 1:  

and the other way around.

QlikView 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 QlikView without the special interpretation function in the script:

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