topic Date format-exporting to excel in QlikView

Date format-exporting to excel

Thu, 07 Oct 2010 10:27:50 GMT

Hi,

On my document I am able to export data to excel. The date on the excel spreadsheet appears as a numbers rather than date.

The code for the consumption period is:

date(MonthStart(CI_BSEG.END_DT),'MMM/YYYY') as ConsumptionPeriod

Should this not work in keeping the date in the date format once exported?

Am I doing something wrong on this?

Thanks in advance,
mckay9999

Date format-exporting to excel

Thu, 07 Oct 2010 11:31:37 GMT

This is normal, Dates format are numbers.

You can change format cell in Excel do display dates directly OR you change the Qlikview dimension to display it as TEXT.

For example TEXT(Date(MyDate,'DD/MM/YYYY')).

In this case export will be done as text and Excel will see it as text too.

Regards,

Sébastien

Date format-exporting to excel

Fri, 28 Jan 2011 00:46:06 GMT

I'd like to add: I think it's fair to call this a bug. If QlikView is asked to export data to Excel which it knows to be date data, it should format it as a date. The awesome one-click convenience of the XL button is ruined when the results are unreadable, and only fairly advanced Excel users will recognize that serial numbers in the range of 40,000+ are probably dates.

Formatting the value as text is a workaround, not a solution, since it ruins sorting.