Skip to main content

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:  

Dates and serial numbers
Date string Date serial number
1997-08-06 35648
09:00 0.375
1997-08-06 09:00 35648.375

and the other way around.

Serial numbers and dates
Date serial number Specified number format Date string
35648 'D/M/YY' 6/8/97
0.375 'hh.mm' 09.00

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:

Date representations
Source data QlikView default interpretation 'YYYY-MM-DD' date format 'MM/DD/YYYY' date format 'hh:mm' time format '# ##0.00' number format
0.375 0.375 1899-12-30 12/30/1899 09:00 0.38
33857 33857 1992-09-10 09/10/1992 00:00 33 857.00
97-08-06 97-08-06 1997-08-06 08/06/1997 00:00 35 648.00
970806 970806 4557-12-21 12/21/4557 00:00 970 806.00
8/6/97 8/6/97 8/6/97 8/6/97 8/6/97 8/6/97

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:

Date representations
Source data QlikView default interpretation 'YYYY-MM-DD' date format 'MM/DD/YYYY' date format 'hh:mm' time format '# ##0.00' number format
0.375 0.375 0.375 0.375 0.375 0.375
33857 33857 33857 33857 33857 33857
97-08-06 97-08-06 97-08-06 97-08-06 97-08-06 97-08-06
970806 970806 970806 970806 970806 970806
8/6/97 8/6/97 1997-08-06 08/06/1997 00:00 35 648.00

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Join the Analytics Modernization Program

Remove banner from view

Modernize without compromising your valuable QlikView apps with the Analytics Modernization Program. Click here for more information or reach out: ampquestions@qlik.com