Export to Excel (Time format problem)

[sanderha]

Hello,

I try to export a table box to Excel, but the Time fields aren't exported correctly.

Example: 8:40:00 (in Qlikview) --> -53322,6388888889 (in Excel).

Table Box Properties:

I use Qlikview 11.00.11282.0 and Office 2013.

Is there anyone who can help me with this?

Kind regards,

Sander

[Mattias_Malré]

Moved this to the QlikView part of the community.

[ashfaq_haseeb]

Hi,

You can try by changing it from time to timestamp.

If possible can you share sample app to look over.

Regards

ASHFAQ

[amit_saini]

Sander,

Please share source data if possible.

Thanks,
AS

[sanderha]

I uploaded an attachment with sample data.

If I export this table to Excel I get this:

[vinay_hg]

before changing the format in table property first convert the raw date field to time filed before loading by using Date#().

[v_iyyappan]

Hi,

You can use Straight Table instead of Tablebox . Please check the attached file........

Regards,

[tresesco]

Your actual field values are negative generating timestamp beyond 30/12/1899 which is actually zero (0). Excel perhaps could not recognize such dates. Hence the issue. You might have to reload data properly formatted.

[ashfaq_haseeb]

Hi,

As your dates are in 70's. QlikView is not properly converting dates or Excel does not recognize such dates..

Can you try to convert your start time and end time field into proper time while fetching data.

I tried to convert in qlikView and its not working somehow.

or may be Marco Wedel can come up with some fantastic expression.

Regards

ASHFAQ

[MarcoWedel · Accepted solution]

Instead of

FACT_Example:

LOAD "Date",   

    "Start Time",

    "End Time";

SQL SELECT Date,   

    "Start Time",

    "End Time"   

FROM "NAV_O_POWER_SUPPORT".dbo."Support$KTN TimeregIT TimeRegistration"

WHERE ("Date">'2014-08-30 00:00:00.000' AND "Date" < getdate())

  AND (Employee = 'HAECK');

maybe

FACT_Example:

LOAD "Date",   

    Time(Frac("Start Time")) as [Start Time],

    Time(Frac("End Time")) as [EndTime];

SQL SELECT Date,   

    "Start Time",

    "End Time"   

FROM "NAV_O_POWER_SUPPORT".dbo."Support$KTN TimeregIT TimeRegistration"

WHERE ("Date">'2014-08-30 00:00:00.000' AND "Date" < getdate())

  AND (Employee = 'HAECK');

?

hope this helps

regards

Marco