topic Re: Negative time values export to Excel as ############ in QlikView

Negative time values export to Excel as ############

mphekin12 — Thu, 20 Aug 2015 12:59:01 GMT

Can someone help with an issue I'm having with negative time values showing up as '#########' when data from a straight table is exported to Excel. As you can see here, the values are correct in QlikView:

But this is what the data looks like once exported to Excel:

Has anyone else had this issue? If so can you tell me what you did to correct it?

Thanks!

Re: Negative time values export to Excel as ############

jonathandienst — Thu, 20 Aug 2015 13:03:32 GMT

See here
how to export qlikview pivot table containing negative hours to excel without replacing those negative value with ######

Re: Negative time values export to Excel as ############

alexandros17 — Thu, 20 Aug 2015 13:03:54 GMT

For sure it is not a qlik problem, check Excel settings for date time ....

let me know

Re: Negative time values export to Excel as ############

mphekin12 — Thu, 20 Aug 2015 13:07:28 GMT

jonathan,

thanks for you suggestion. i did look at that post but really didn't see a solution.

Re: Negative time values export to Excel as ############

mphekin12 — Thu, 20 Aug 2015 13:09:11 GMT

alessandro,

i would rather not have to resort to changing excel settings. it would be ok if i needed to change my settings but to expect hundreds of end users to do the same would not be the best work around. any other ideas?

Re: Negative time values export to Excel as ############

mphekin12 — Thu, 20 Aug 2015 13:30:31 GMT

Hey all,

If anyone is having the same issue, I found a work around. I created this formula for the Variance Between A and B column:

=if(interval(B - A, 'hh:mm') < 0,
'-' & interval(fabs(B - A), 'hh:mm'),
interval(B - A, 'hh:mm')
)

The same formula can be used for the Variance Between A and C column just replace the B with a C.

Here is what the table will look like in QV:

And the data exported out to Excel now looks like this:

It appears that the Variance columns are now formatted as text, but the data is correct.

I hope this might help someone in the future!