Issue Converting Qlikview Time Interval Format to Excel

[#1]

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.

[#2]

Any ideas?

[#3]

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

Regards--

Nayan

[#4]

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.

[#5]

Hi...

Has there been any solution to this...

Please suggest..

Thanks in advance...

K

[#6]

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

[#7]

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

[#8]

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