Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] EXL2K recognising date formats

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPENED] EXL2K recognising date formats
 Login/Join
 
pwoods
Member
posted
I am trying to get a date format into excel that includes the short name for the month (ideal is DD Mmm YYYY)

The user needs to be able to re-format the date within excel and sort it correctly - so excel needs to recognise that it is handling a date.

I have been playing around with this code but cannot get excel to play ball.

DEFINE FILE CAR
DATE0/YYMD='20081225';
DATE1/I8DMtYY=DATE0;
DATE2/DMtYY=DATE0;
DATE3/DMYY=DATE0;
END

TABLE FILE CAR
PRINT DATE0 DATE1 DATE2 DATE3
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END

Using this code DATE0 and DATE3 are recognised and the I can re-format them to whatever date format I want. But I cannot do this with DATE1 or DATE2.

Anyone have any suggestions as to how I might acheive this?

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


AIX Server
WebFocus 7.6.11
 
Posts: 10 | Location: Nottingham, UK | Registered: November 20, 2008Report This Post
Darin Lee
Virtuoso
posted Hide Post
As far as I know Excel does not recognize dates that have translated components. It just assumes that they are strings since that is the actual content of the cell as delivered by WF, as opposed to a normal numeric date that has had an Excel formatting option applied. You might try looking into using an Excel template with a pre-formatted column. I'm sure there are other suggestions that people have used as well.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Interestingly, for DATE1, if you put your cursor at the end of the date, and then press enter, the data converts to a date.


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, 2005Report This Post
Darin Lee
Virtuoso
posted Hide Post
And why, if you indicate the lower case "t" whcih means to use mixed case, does it not create the date with mixed case? To go one step further, if you change the date format to MtDYY instead of DMtYY, it doe NOT convert to a date when pressing enter after the date. The inconsistencies would lead me to open a case with techsupport.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Interestingly, if you use HTMTABLE with HTMLFORMTYPE=XLS, dates DATE0 and DATE1 are recognized in Excel as dates while DATE2 and DATE3 are seen as strings (although DATE0 has an MDYY format instead of YYMD).

DEFINE FILE CAR
 DATE0/YYMD='20081225';
 DATE1/I8DMtYY=DATE0;
 DATE2/DMtYY=DATE0;
 DATE3/DMYY=DATE0;
END
-*
TABLE FILE CAR
 PRINT DATE0 DATE1 DATE2 DATE3
 BY COUNTRY
 ON TABLE HOLD FORMAT HTMTABLE
END
-*
SET HTMLFORMTYPE=XLS
-RUN
-*
-HTMLFORM BEGIN
!IBI.FIL.HOLD;
-HTMLFORM END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
<JG>
posted
Use this cut down example from the devstudio help

DEFINE FILE EMPLOYEE
YRHIRED/YYMD = HIRE_DATE;
MHIRED/I8DMtYY = YRHIRED;
END
TABLE FILE EMPLOYEE
PRINT FIRST_NAME
MHIRED
BY LAST_NAME
ON TABLE PCHOLD FORMAT EXL2K
END

In 7.6.10 this works although you will get a warning about sorting numbers displayed as text
 
Report This Post
pwoods
Member
posted Hide Post
Thanks to everyone for their help and suggestions.

I will raise a case with IB support as I don't understand why a user cannot reformat DATE1 or the inconsistencies with the format as Darin has identified.

I have been able to use other applications to send date in format DD MMM YYYY to excel and users have been able to re-format them without any issue, so I don't think this is an excel problem.

Using templates to deal with this isn't really practical, as the number and position of my date columns changes report by report and with each deployment...


AIX Server
WebFocus 7.6.11
 
Posts: 10 | Location: Nottingham, UK | Registered: November 20, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE-OPENED] EXL2K recognising date formats

Copyright © 1996-2020 Information Builders