Why it is adding a day to the date data when exported into excel in QV V10 R2

[#1]

Hi,

Has any one encounter this issue and is there something it can be done to prevente it?

When data is exported to Excel, the date is changed - it becomes a day later.  For Example, the data below in QlikView V10 R2 shows dates of 7/3/2011 and 7/10/2011 and when exported to excel it shows 7/4/2011 and 7/11/2011 respectively

Transfer to Excel the date changes to a day later...

THD Year	THD Month	Week End Date	Item name	Item number
2011	Jul 2011	7/4/2011	8x205x1210 THD Cherry ST54 AC3	367211-00099
2011	Jul 2011	7/11/2011	8x205x1210 THD Cherry ST54 AC3	367211-00099
2011	Jul 2011	Total
2011	Total

Thanks for your help.

Miguel Mena

This is an Scramble sample: 

[m_woolf]

Maybe you could attach a sample qvw file that demonstrates the problem.

[#3]

Hi,

I just posted an scramble sample. Thanks.

I noticed that the behavior it is only occurring on Version 10 and not

with version 9

Miguel Mena | Business Intelligence Specialist

Clarion Industries | Clarion Laminates, LLC | Clarion Boards, Inc

919-653-7886 (p) | 919-653-6054 (f) | 919-609-9229 (c)

Miguel.Mena@ClarionIndustries.com

<mailto:Miguel.Mena@ClarionIndustries.com

[m_woolf · Accepted solution]

I see the same behavior in ver 9 SR6.

When I Send to Excel, set the format to general, and increase the number of decimal places I get this:

Week End Date	Cartons Sold	Cartons on Hand
40671.99999998840	-41,396	245,967
40678.99999998840	-41,017	246,918
40685.99999998840	-41,144	247,019
40692.99999998840	-40,866	246,313
40699.99999998840	-41,733	246,779
40706.99999998840	-41,551	248,388
40713.99999998840	-41,412	247,980
40720.99999998840	-40,777	245,063
40727.99999998840	-40,281	242,752
40734.99999998840	-41,101	246,484

I suspect the QlikView is showing the first date as 40671 and Excel is rounding to 40672.

Maybe you could use date(floor(DDWeekendDate)) to remove the decimals?

[#5]

Thank you!  It worked...