Date changes to Number when export to Excel

[#1]

I've following code that generates a Calendar QVD. That QVD is then referred by other QV applications as input. Chart within QV displays date/months/quarters/years as desired format. However, when the values in chart is exported to excel, date format changes to numbers.

start month = 40694

for i = 0 to 26;

let vLoop_Month=Num(DayStart(MonthEnd(addmonths(StartMonth,-i))));
let vLoop_Quarter=Num(DayStart(QuarterEnd(addmonths(StartMonth,-i))));

MasterCalendar:
load $(vLoop_Month) as DateKey,
     Month($(vLoop_Month)) as CM, 
     'Q'&ceil(Month($(vLoop_Month))/3) as CQ,
     Year($(vLoop_Month)) as CY,
     dual(Year($(vLoop_Quarter))&'-'&'Q'&ceil(Month($(vLoop_Quarter))/3),$(vLoop_Quarter)) as CYQ,
     dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)) as CYM,
     Date(dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)), 'M/YY') as CYM2,
     dual('Q'& ceil(Month($(vLoop_Quarter))/3)&'/'&date($(vLoop_Quarter),'YY'),$(vLoop_Quarter)) as CYQS
autogenerate(1);

Next

Store above into Calendar.qvd

Other QVWs just reads the Calendar QVD. So all the fields such as CM, CQ, CY, CYQ, CYM, CYM2, and CYQS are displayed as desired date format in QV. However, when I export them to excel they are converted to numbers. Any help will be appreciated. Thanks

[nagaiank]

Check if the added field 'ExtDate' appears in Excel in the date format.

StartMonth = 40694;

for i = 0 to 26;

let vLoop_Month=Num(DayStart(MonthEnd(addmonths($(StartMonth),-i))));

let vLoop_Quarter=Num(DayStart(QuarterEnd(addmonths($(StartMonth),-i))));

MasterCalendar:

load $(vLoop_Month) as DateKey,

     Date($(vLoop_Month),'MM/DD/YYYY') as ExtDate,

     Month($(vLoop_Month)) as CM, 

     'Q'&ceil(Month($(vLoop_Month))/3) as CQ,

     Year($(vLoop_Month)) as CY,

     dual(Year($(vLoop_Quarter))&'-'&'Q'&ceil(Month($(vLoop_Quarter))/3),$(vLoop_Quarter)) as CYQ,

     dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)) as CYM,

     Date(dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)), 'M/YY') as CYM2,

     dual('Q'& ceil(Month($(vLoop_Quarter))/3)&'/'&date($(vLoop_Quarter),'YY'),$(vLoop_Quarter)) as CYQS

autogenerate(1);

Next

[#3]

Go to "User Preferences" --> "Export Tab"   select "Full formatting" and see if that helps .....

[#4]

Hi there,

The added field appears as date, but I cannot use that for Quarters and other formats. What will be the date format for CQ, CYQ or CYQS in the above code? The other thing I've tried is converting everything other field, except the date field to text. This time it exports to excel in the desired format, but sorting in the charts within QV is messed up. For some reason I can't get QV and Excel to work at the same time. Thanks for your help.

[nagaiank]

Try the following script and it works for me.

StartMonth = 40694;

for i = 0 to 26;

let vLoop_Month=Num(DayStart(MonthEnd(addmonths($(StartMonth),-i))));

let vLoop_Quarter=Num(DayStart(QuarterEnd(addmonths($(StartMonth),-i))));

MasterCalendar:

load $(vLoop_Month) as DateKey,

     Date($(vLoop_Month),'MM/DD/YYYY') as ExtDate,

     Month($(vLoop_Month)) as CM,

     'Q'&ceil(Month($(vLoop_Month))/3) as CQ,

     Year($(vLoop_Month)) as CY,

     Text(Year($(vLoop_Quarter))&'-'&'Q'&ceil(Month($(vLoop_Quarter))/3)) as CYQ,

     Text(Year($(vLoop_Month))&'-'&Month($(vLoop_Month))) as CYM,

     Date(dual(Year($(vLoop_Month))&'-'&Month($(vLoop_Month)),$(vLoop_Month)), 'M/YY') as CYM2,

     Text('Q'& ceil(Month($(vLoop_Quarter))/3)&'/'&date($(vLoop_Quarter),'YY')) as CYQS

autogenerate(1);

Next

[#6 · Accepted solution]

Property of line chart prevents me from exporting custom dates as dates and it converts them to number. Once I converted the line chart into Straight table and then exported, it started working as expected. So from now on every chart that needs exporting we ended up giving Fast Change option to clients.