Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issue Converting Qlikview Time Interval Format to Excel

I'm having difficulty when exporting a QV object to Excel. The Expression in question is of type Interval dd:hh:mm format but when coverting to Excel, any value greater than 31 days seems to be truncated down to a value 31 days or under.

Additionally, excel can't seem to handle negative Time Intervals and expresses them as '#########'.

The format I'm using in excel is Custom dd:hh:ss but it's obviously not working.

Any help would be greatly appreciated.

Regards,

Jim.

8 Replies
Not applicable
Author

Any ideas?

Not applicable
Author

Can you please share application with that object from which you want to export to excel

Regards--

Nayan

Not applicable
Author

Hi,

Sorry but I'm not familiar with how to share an application with an object. Can you provide some brief steps please?

Regards,

Jim.

Not applicable
Author

Hi...

Has there been any solution to this...

Please suggest..

Thanks in advance...

K

Not applicable
Author

IN QLIKVIEW

write your datefield as 

date(date#(datefield,'DD/MM/YYYY'),'MM/DD/YYYY')

HERE bold format set according to your excel format. then export as excel

hope this helps you

Regards

vishwaranjan

Not applicable
Author

Hi Ranjan...

It is actually a negative time interval represented in QlikView using Interval() and is correct, e.g., -03:00 which means -3 hours. However, on exporting to excel, it is shown as some negative value like -0.25 something but in the actual cell value it is like '#############'.

So, is there any way in QlikView to convey to excel with which excel can depict the negative time interval in appropriate format??

Thanks and Regards,

K

Not applicable
Author

try to write your interval() within NUM#()  then export in excel.

or text() function

example-- =Num#(interval('02/02/13' - '02/03/13'))

example-- =Text(interval('02/02/13' - '02/03/13'))

marcus_sommer

It is an excel feature that negative time-values are not possible, unless you changed global excel-settings whereby all date-values changed (not really recommended).

Only for display you could convert in excel this value in a string and hid this colums. All another calculation must be on the originally columns.

if(time<0;"-"&text(time * -1, "hh:mm:ss");time)

- Marcus