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     [SOLVED] Date Field In Excel

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Date Field In Excel
 Login/Join
 
CathyB
Silver Member
posted
Hi I have research the forum and have some some information concerning zero or blank date fields converting correctly in Excel (I'm getting a return value of 12/31/1900 on any record that should be zero or blank. I have tried all the suggestion that I've seen; applying missing on Set nodata = '' or 0 - basically everything to no avail I'm hoping someone can further explain or help me fix this problem. Here is my define statement:
/DEFINE FILE OPENTERMS
NetDays/D12=IF EMS_FL EQ 'EM' THEN 10 ELSE TMDUDA;
TestingDate/I8=ShipDate;
CheckDate1/I8=IF POTRMS NE 103 THEN TestingDate ELSE 0;
CheckDate2/I8YYMD = AYMD(CheckDate1,NetDays,'I8YYMD');
CheckDate/I8MDYY = DATECVT(CheckDate2,'I8YYMD','I8MDYY');/

I get a zero to populate all the way up to CheckDate2 as soon as I start applying a date format it wants to return a date of 12/31/1900 in excel. For simplicity I have remove any attempts of using "Missing on" or If 0 Then 0;

Any help would be greatly appreciated.

Cathy

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


WEBFOCUS 7.14
WEBFOCUS.8.04
 
Posts: 44 | Registered: March 30, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Try changing the final Date field to alpha:

DEFINE FILE CAR
TestingDate/I8 = 20090831;
CheckDate1/I8=IF COUNTRY CONTAINS 'E' THEN TestingDate ELSE 0;
CheckDate2/I8YYMD = AYMD(CheckDate1,44,'I8YYMD');
CheckDate/A8MDYY = DATECVT(CheckDate2,'I8YYMD','A8MDYY');/

END

TABLE FILE CAR
PRINT
TestingDate
CheckDate1
CheckDate2
CheckDate
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END


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
CathyB
Silver Member
posted Hide Post
Your example gives me the same result.

COUNTRY TestingDate CheckDate1 CheckDate2 CheckDate
ENGLAND 20090831 20090831 2009/10/14 10/14/2009
FRANCE 20090831 20090831 2009/10/14 10/14/2009
ITALY 20090831 0 1900/12/31 12/31/1900
JAPAN 20090831 0 1900/12/31 12/31/1900
W GERMANY 20090831 20090831 2009/10/14 10/14/2009

I get a zero value in the "CheckDate1' Field but CheckDate2 and the final CheckDate is still returning 12/31/1900 in excel. Any other suggestions??


WEBFOCUS 7.14
WEBFOCUS.8.04
 
Posts: 44 | Registered: March 30, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
When I run my sample code, an Excel spreadsheet opens in MS Excel 2010, with blank cells where there is no date. I saved the spreadsheet in xlsx, xls and csv formats and reopened them and the blank cells are still blank.

In which scenario do you see the "12/31/1900"?


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
CathyB
Silver Member
posted Hide Post
I am using Excel 2003. I did get it to finally work. This is what I had to do...
DEFINE FILE CAR
TestingDate/I8 = 20090831;
CheckDate1/I8 MISSING ON=IF COUNTRY CONTAINS 'E' THEN TestingDate ELSE MISSING;
CheckDate2/I8YYMD MISSING ON = IF CheckDate1 NE MISSING THEN AYMD(CheckDate1,44,'I8YYMD') ELSE MISSING;
CheckDate/A8MDYY MISSING ON =IF CheckDate1 NE MISSING THEN DATECVT(CheckDate2,'I8YYMD','A8MDYY') ELSE MISSING;
END

This originally work perfectly in your example but it still was not working in my define. THEN I realized my
CheckDate was a BY field - as soon as I'm made it a print field it worked perfectly. Should of known better!!!

Thanks so much you were a huge help Francis!!


WEBFOCUS 7.14
WEBFOCUS.8.04
 
Posts: 44 | Registered: March 30, 2007Report 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     [SOLVED] Date Field In Excel

Copyright © 1996-2020 Information Builders