• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

Exporting to Excel shows null date as 1/1/1900

This is my formula:

if {EMPLOYEE.TERM_DATE} <> #1/1/1700# then {EMPLOYEE.TERM_DATE} else
if {EMPLOYEE.FST_DAY_WORKED} <> #1/1/1700# then {EMPLOYEE.FST_DAY_WORKED}

When I export the results to Excel, the null values (1/1/1700) are showing up as 1/1/1900.  Any assistance would be much appreciated.
0
jph826
Asked:
jph826
  • 2
  • 2
1 Solution
 
mlmccCommented:
In Crystal NULL dates will default to 1/1/1900.

Why if they are NULL are you testing for 1/1/1700

I believe Excel also defaults the dates to 1/1/1900

mlmcc
0
 
jph826Author Commented:
Basically what the formula should do is look at the term date field, if it is not null, then show the term date, else if the first day worked field is not null, then show the first day worked date.

This will be a scheduled report with an Excel output for the user.  Is there a way to avoid the null values showing up (in the export to Excel) as anything other than a blank cell?
0
 
mlmccCommented:
Try a formula like

If NOT IsNull({EMPLOYEE.TERM_DATE}) then
    {EMPLOYEE.TERM_DATE}
Else If Not IsNull({EMPLOYEE.FST_DAY_WORKED})  then
    {EMPLOYEE.FST_DAY_WORKED}
Else
    Date(1900,1,1)

Open in new window


mlmcc
0
 
jph826Author Commented:
Thanks.  That formula should have worked but it did not return any data.  For example, employee Jane had a blank Term_Date field, but the Fst_Day_Worked field was populated with 5/21/2013.  I expect the formula to return 5/21/2013 but it did not.  My initial formula returned the correct data, the only issue was when exporting to Excel.

At any rate, I added "Else Date(1900,1,1)" to the end of my original formula and it returned the correct results and the export to Excel no longer shows 1/1/1900 for the null values.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now