Skip to main content


The Design blog is all about product and Qlik solutions, such as scripting, data modeling, visual design, extensions, best practices, and more!

Skip the ticket, Chat with Qlik Support instead for instant assistance.

What is the difference between the Date# function and the Date function? These two functions seem quite similar, but they have different purposes. It took me a while to understand when to use one over the other. Now that I have a handle on it, I thought I would share what I have learned. The Date# function is an interpretation function. According to Qlik Help, “Date# evaluates an expression as a date in the format specified in the second argument, if supplied.” I use the Date# function when I am loading a value that I want to be perceived as a date. For example, in the partial script below, I loaded “YR” from an Excel file and to ensure that the value was evaluated as a date with the year format ‘YYYY’, I used the Date# function.




The syntax of the Date# function is as follows:

Date#(text[, format])

The format parameter of the Date# and Date functions is optional. If it is not included in the expression, it uses the date format set in the system variables in the script. Using Date#() is an important step if the “Year” field is used later in the script to join data or to compare to data that has a date value. When evaluating 2 values, you want to ensure that they are formatted the same and that you are comparing apples to apples.

The Date function is a formatting function. According to Qlik Help, “Date() formats an expression as a date using the format set in the system variables in the data load script, or the operating system, or a format string, if supplied.” I use the Date function to format a date a specific way. For example, I may format a date as ‘YYYY’ if I only am interested in seeing the year. I could also format the date like this ‘M/D/YYYY’ to see the month, day and year.

The syntax for the Date function is as follows:

Date(text[, format])

In the example below, I am formatting the “Yr” field as a 4-digit year (i.e. 2021).



I can also format a date as seen below. This expression will return 09/17/2021.



On occasion, I have used both the Date# and the Date functions in the same expression. For example, if I am loading text with the format YYYYMM and I want to format it as MMM-YYYY, I cannot simply use the expression Date(text, ‘MMM-YYYY’) because it does not pick up that the text is a date. So, I need to first interpret the text as a date and then I can format it. This expression works:

Date(Date#(text, ‘YYYYMM’), ‘MMM-YYYY’)

The Date# function first identifies the text as a date and indicates the format the date is in (‘YYYYMM’). Then the Date function formats the text like this ‘MMM-YYYY’.

Both the Date# and Date functions can be used in script and chart functions. Date#() interprets the data as a date and Date() formats the date as specified. I hope this was helpful.