Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] Dates in EXL2K - incorrect format
Go
New
Search
Notify
Tools
Reply
  
[CASE-OPENED] Dates in EXL2K - incorrect format
 Login/Join
 
Platinum Member
posted
I have a report that outputs to EXL2K but the fields that are HYYMDS (datetime fields) have a general format in Excel. Is there a way to make this as a datetime field?

I checked the forum and I only saw examples of dates not datetimes conversion in Excel.

Thanks for your help.

Dan

This message has been edited. Last edited by: dballest,


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Dan :
I believe you can get around that issue my making sure the field is defined as a character field before it is passed on to Excel.

If you are using SQL passthru, you can --
CHAR(field-name)
If you are using FOCUS code, you can --
COMPUTE/DEFINE using EDIT(fieldname)

Sandeep Mamidenna


-------------------------------------------------------------------------------------------------
Blue Cross & Blue Shield of MS
WF.76-10 on (WS2003 + WebSphere) / EDA on z/OS + DB2 + MS-SQL
MRE, BID, Dev. Studio, Self-Service apps & a dash of fun !! Music
 
Posts: 218 | Location: Jackson, MS | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks for the reply Sandeep.

It did not work for me. It's still showing as General Format in Excel. Below is the sample code:

TABLE FILE RV_DMRHOLDS_ETL
PRINT
COMPUTE ODATE1/A20=HCNVRT(ORIGINATIONDATE, '(HYYMDS)', 20, 'A20');
ORIGINATIONDATE/HMDYYS
WHERE RECORDLIMIT EQ 5
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT EXL2K
END


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I tried the fex below but the date field is not coming up as a date in Excel 2003. Any suggestions appreciated. Thanks.

-SET &TDATE = '05/12/2005 16:45';

DEFINE FILE CAR
TDATE1/A20 = '&TDATE.EVAL';
TESTDATE/HYYMDS=HINPUT(20, TDATE1, 10, TESTDATE);
END

TABLE FILE CAR
PRINT
CAR
TDATE1
TESTDATE
ON TABLE PCHOLD FORMAT EXL2K
END

-EXIT


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
I have not yet succeeded in passing a date-time format to Excel. In the example below, only the date-formatted fields have a non-general Excel format.

DEFINE FILE CAR
DATETIME/HYYMDS = HINPUT(20, '2005/12/29 16:45.000', 10, DATETIME);
STARTDATE/YYMD  = HDATE(DATETIME, 'YYMD');
END
-RUN

TABLE FILE CAR
PRINT
STARTDATE AS SD
STARTDATE/A8DMYY AS SD1
STARTDATE/A8MDYY AS SD2
STARTDATE/MTDYY AS SD3
STARTDATE/MTRDYY AS SD4
STARTDATE/MtDYY AS SD5
STARTDATE/MtrDYY AS SD6
STARTDATE/MtrYY AS SD7
STARTDATE/QYY AS SD8
STARTDATE/YY AS SD9
STARTDATE/wMtDYY AS SDA

COMPUTE SD1C/A8DMYY = STARTDATE;
COMPUTE SD2C/A8MDYY = STARTDATE;
COMPUTE SD3C/MTDYY = STARTDATE;
COMPUTE SD4C/MTRDYY = STARTDATE;
COMPUTE SD5C/MtDYY = STARTDATE;
COMPUTE SD6C/MtrDYY = STARTDATE;
COMPUTE SD7C/MtrYY = STARTDATE;
COMPUTE SD8C/QYY = STARTDATE;
COMPUTE SD9C/YY = STARTDATE;
COMPUTE SDAC/wMtDYY = STARTDATE;

DATETIME AS DT
DATETIME/HDMYYS AS DT1
DATETIME/HHIA AS DT2
DATETIME/HHIS AS DT3
DATETIME/HHISa AS DT4
DATETIME/HHIa AS DT5
DATETIME/HM AS DT6
DATETIME/HMDYYS AS DT7
DATETIME/HMTDYYSA AS DT8
DATETIME/HMtDYY AS DT9
DATETIME/HMtDYYSA AS DTA
DATETIME/HMtYY AS DTB
DATETIME/HYM AS DTC
DATETIME/HYMD AS DTD
DATETIME/HYYM AS DTE
DATETIME/HYYMD AS DTF
DATETIME/HYYMDSA AS DTG

COMPUTE DT1C/HDMYYS = DATETIME;
COMPUTE DT2C/HHIA = DATETIME;
COMPUTE DT3C/HHIS = DATETIME;
COMPUTE DT4C/HHISa = DATETIME;
COMPUTE DT5C/HHIa = DATETIME;
COMPUTE DT6C/HM = DATETIME;
COMPUTE DT7C/HMDYYS = DATETIME;
COMPUTE DT8C/HMTDYYSA = DATETIME;
COMPUTE DT9C/HMtDYY = DATETIME;
COMPUTE DTAC/HMtDYYSA = DATETIME;
COMPUTE DTBC/HMtYY = DATETIME;
COMPUTE DTCC/HYM = DATETIME;
COMPUTE DTDC/HYMD = DATETIME;
COMPUTE DTEC/HYYM = DATETIME;
COMPUTE DTFC/HYYMD = DATETIME;
COMPUTE DTGC/HYYMDSA = DATETIME;

BY COUNTRY

WHERE COUNTRY EQ 'ENGLAND'

ON TABLE PCHOLD FORMAT EXL2K
END
-RUN


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Francis,

Thanks for the reply. I guess I'll open a case and see if this functionality is anywhere near the horizon.

Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Reply With QuoteReport This Post
Master
posted Hide Post
I just ran Francis' sample fex. The datetime fields are not in actual excel date/time format, but they displayed properly. For example DTGC displays as "2005/12/29 4:45:00PM". Excel sees it as general text, not a date/time field. I assume the problem is that, while Excel displays the field properly, Excel sees it at general text instead of date/time.

Just for the record, Excel see the QYY as general text and the YY as a zip code, all of the others, Excel sees as date fields.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 956 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
That is correct jgelona. The customer has a Excel application with macro (vb scripts). It's expecting the cell to be a date but it is recognized as general text.

Opened a case with IBI and it's been on their plate for awhile (NFR) and not sure when it will be out.

In the meantime, the macro code had to be changed to add an intermediate step to convert the cell to a data-time type.

Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Reply With QuoteReport This Post
<JG>
posted
Reference: Usage Notes for Date and Numeric Formats

The following formats are not supported in EXL2K.
They will translate into Excel General format and possibly produce unpredictable results:

YY, Y, M, D, JUL, and I2MT.

Any date format with a Q (quarter).

Any packed-decimal (P) date formats.

Any alphanumeric (A) date formats.

Fixed Dollar (N) formats.

Multiple format options.
Only single format options are supported when using FORMAT EXL2K.
For example, the formats I9C and I9B are supported, but I9BC is not.

The following applies to headings and footings with embedded numeric fields:

If you embed a numeric field in a heading, subheading, footing, or subfooting of a report in Excel 2000 or higher format,
the numeric field displays in Excel general format (text).

To display a numeric field in Excel number format, you must set HEADALIGN=BODY in the StyleSheet.



Dev studio on-line help

EXL2K display format and formatted dates

This message has been edited. Last edited by: <JG>,
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
I just tried:
SUM
     MIN.DET_FIRSTDETECTDATETIME/HYYMDS
     MAX.DET_LASTDETECTDATETIME/HYYMDS
BY SITE
BY TAGCODE
ON TABLE PCHOLD FORMAT EXL2K
END

with output to EXL2K and was able to do perform date math in Excel without reformatting the date columns. I subtracted LastDetectDateTime (D1) from FirstDetectDateTime (C1)and got the correct answer (in hours).
 
Posts: 60 | Location: Ellensburg Washington | Registered: May 22, 2009Reply With QuoteReport This Post
Master
posted Hide Post
At least no one suggested using templates and saved the OP from having to reply that those wouldn't really work in his situation. Smiler

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Reply With QuoteReport This Post
<JG>
posted
That's what the documentation says.

(late on the submit button - refers to previous post)
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks to those who replied.

To M Meagher, did you get the HYYMDS field to appear as a non-general type in Excel using the posted CAR fex above?

Thanks,
Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Reply With QuoteReport This Post
<JG>
posted
7.7.0.1 format is shown as being general
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
It stays in 'General' format. Interestingly, Excel treats the columns as if they were date/time format if you use them in a date function. I also tried 'ON TABLE PCHOLD FORMAT EXL07'. It also created the columns as 'General'.
 
Posts: 60 | Location: Ellensburg Washington | Registered: May 22, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
Did anyone find the solution for date formate in Excel? I know it is possible to bring in custom, text and general formate but not in Date itself. did this problem get resolved?

Arif


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] Dates in EXL2K - incorrect format

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.