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

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)
GrapeladyAsked:
Who is Participating?
 
GrapeladyConnect With a Mentor Author Commented:
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
 
mlmccCommented:
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
 
GrapeladyAuthor Commented:
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
 
GrapeladyAuthor Commented:
Please close the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.