date format not correct in excel

For questions regarding programming in the EViews programming language.

Moderators: EViews Gareth, EViews Jason, EViews Moderator, EViews Matt

BT454
Posts: 135
Joined: Wed Aug 02, 2017 6:07 am

date format not correct in excel

Postby BT454 » Wed Feb 10, 2021 9:11 am

Hi all!
I'm exporting some unstructured data to excel. One of the series is a stacked "period" series, that is the stacked date.
It seems no matter which way I try to format the data excel is reading it incorrectly.
Here is the workfile and code.
Any and all help is, as always greatly, appreciated!
Thanks!
Bob

Code: Select all

wfopen usl48_stdata
%exstart = "2016"
%exend = hsto001_1.@last
wfcreate(wf=gpcm_storage, page=data) m %exstart %exend
alpha stor_plan="February2021"
copy usl48_stdata::export\hsto*_1 gpcm_storage::data\hsto*_1
copy usl48_stdata::export\lsto*_1 gpcm_storage::data\lsto*_1
wfclose usl48_stdata
wfopen can_stdata
copy can_stdata::export\hsto*_1 gpcm_storage::data\hsto*_1
copy can_stdata::export\lsto*_1 gpcm_storage::data\lsto*_1
wfclose can_stdata
wfsave gpcm_storage
group g_hex hsto*_1
group g_lex lsto*_1
series period=@dateval(@datestr(@date))
pagestack(page=hsto) h? @ h? period Stor_Plan
alpha stor_code = @left(var01,6)
rename h Max_Qty
group g_copy period stor_code max_qty Stor_Plan
pageselect data
pagestack(page=lsto) l? @ l?
rename l min_qty
!end = @obsrange
pagecreate(page=export) u 1 !end
copy(g=d) hsto\g_copy export\g_copy
copy lsto\min_qty export\min_qty
pagesave(t=excel, mode=update) c:\warem32\gpcm_data_lto.xlsx range="input" @keep Stor_Plan Stor_Code Period Min_Qty Max_Qty

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: date format not correct in excel

Postby EViews Gareth » Wed Feb 10, 2021 9:47 am

You'll need to provide more details.
Follow us on Twitter @IHSEViews

BT454
Posts: 135
Joined: Wed Aug 02, 2017 6:07 am

Re: date format not correct in excel

Postby BT454 » Wed Feb 10, 2021 11:19 am

No problem.
I've attached an excel file that has the unformatted period series, and the series formatted as "mm/dd/yyyy". The formatted dates do not match the dates in the workfile, and are incorrect. I've also attached the workfile containing the period series, for reference.
I'd like for the period series to export to excel, in a way that it will correctly format to "mm/dd/yyyy". Please let me know if there is any other information that would be helpful, and thank you!
Attachments
eviews_dates.xlsx
(2.81 MiB) Downloaded 163 times
gpcm_storage.wf1
(25.61 MiB) Downloaded 140 times

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: date format not correct in excel

Postby EViews Gareth » Wed Feb 10, 2021 3:07 pm

Create a string variable with the date how you want it:

Code: Select all

alpha mydate = @datestr(period, "MM/DD/YYYY")

Then save that to the Excel file rather than the period series.
Follow us on Twitter @IHSEViews

BT454
Posts: 135
Joined: Wed Aug 02, 2017 6:07 am

Re: date format not correct in excel

Postby BT454 » Wed Feb 10, 2021 8:26 pm

thank you sir!


Return to “Programming”

Who is online

Users browsing this forum: No registered users and 31 guests