Dates as Numbers?

[#1]

Hi, I have extracted some dates from a spreadsheet using the crosstab converter because the dates are columns. The data all comes out OK, but the dates always come through as numbers.

How do I either extract the data as a date, or convert the numbers back to dates within the document?

I have tried many date functions such as 'date' and 'date#' with & without formatting strings, all fail & return null values.

Example number : 30/09/2006 is coming out as 38990 (all settings are UK)

Thanks,

Alan Cobbald

[hic]

All dates are numbers in QlikView. What has happened here, is that the formatting is lost. Try

     Date(Date,'DD/MM/YYYY') as Date

in the script, or format it using the list box properties.

Read more on http://community.qlik.com/docs/DOC-3102

HIC

[#3]

Thank you Henric, but that is what I had already tried. All such functions return nulls.

Interestingly, the document says to check if QlikView has interpreted the data as a number, and it hasn't. When I create a list box, the values are left justified, implying that they are strings. It doesn't say how to switch them back to being a number. The num(date) function fails.

Ex_date,date(date#(Ex_date),'DD/MM/YYYY')  as Date1

This is my entry in the load script. Ex_date is the field name for the date values, Date1 is always null whichever date function(s) I use

Th

[MK_QSL]

Please load your sample script with document.. i.e. QVW file.

[#5]

Hello, Try :    date(num(Ex_date),'DD/MM/YYYY');

Regards.

[#6 · Accepted solution]

Thank you all who offered advice, date(num#(.....  was the answer