Solved

Crystal XI, exporting null date fields to EXCEL without the 01/00/1900

Posted on 2014-02-13
6
929 Views
Last Modified: 2014-04-02
I am trying to export from Crystal Xi a date field, based on a grouping, and the most current sequence of dates on my data, In order to get the most current date I have found setting up the field as a date and do a summary on Maximum to the group and just exporting the date field is giving me the right information, BUT...  when I export to Excel if the field is blank the result in Excel is 01/00/1900.  If I change the date to a text field with the else "" I do not have this problem, BUT the most current date does not appear.   Please advise.

Formula:

if ({EMSI_EMPLOYEE_SKILLS.SKILL_CODE} = "SSF" and
{EMSI_EMPLOYEE_SKILLS.USER_DATE_1} <> Date(0,0,0)) then
{EMSI_EMPLOYEE_SKILLS.USER_DATE_1} else Date(0,0,0)
0
Comment
Question by:Grapelady
  • 3
6 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39858075
Date(0,0,0) is 1 Jan 1900

Try it this way as strings

if ({EMSI_EMPLOYEE_SKILLS.SKILL_CODE} = "SSF" and 
{EMSI_EMPLOYEE_SKILLS.USER_DATE_1} <> Date(0,0,0)) then
    CStr({EMSI_EMPLOYEE_SKILLS.USER_DATE_1},ÿyyyMMdd") 
else 
    ""

Open in new window


mlmcc
0
 

Author Comment

by:Grapelady
ID: 39893613
I have attached a document showing what I am trying to accomplish. The suggestion you gave above I am receiving an error, see attached document.
Date-Formula-issue.docx
0
 

Accepted Solution

by:
Grapelady earned 0 total points
ID: 39893655
I think I just found a solution, if you place a suppression on the field formula for the date and if the date value is 0 then it will suppress those records that do not have a date:  

Date field = Date(0,0,0) it will not export 01/00/1900 to Excel if the value is zero.

I don't know if anyone wants to confirm this or not, but it is working.
0
 

Author Comment

by:Grapelady
ID: 39968339
Please close the question.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question