Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
sanderha
Contributor II
Contributor II

Export to Excel (Time format problem)

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:

table_box_properties.png

I use Qlikview 11.00.11282.0 and Office 2013.

Is there anyone who can help me with this?

Kind regards,

Sander

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

11 Replies
Mattias_Malré
Employee
Employee

Moved this to the QlikView part of the community.

I accept cookies.
ashfaq_haseeb
Champion III
Champion III

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
Master III
Master III

Sander,

Please share source data if possible.

Thanks,
AS

sanderha
Contributor II
Contributor II
Author

I uploaded an attachment with sample data.

If I export this table to Excel I get this:

Excel.png

vinay_hg
Creator III
Creator III

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

v_iyyappan
Specialist
Specialist

Hi,

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

Regards,

tresesco
MVP
MVP

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.

Screen Shot 09-02-14 at 03.59 PM 001.PNG.png

ashfaq_haseeb
Champion III
Champion III

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

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