The underlying database we are using (Oracle) uses 01/01/0001 as a default date. When we pass this date to Excel it shows up as an overflow cell because its number value is negative 695000 (more or less). We do not want to convert the date to alpha because a subsequent Excel sort will not preserve chronological order. Any ideas?

How about just converting it to the base date of your epoch. if you're unix, i believe its january 1 1970. if you're windows, i believe its january 1 1900; those dates will convert nicely to focus smart dates with non-negative values.
Does that work for you, Dennis?

DB2 has something very similar, so if I want to surpress it, I check for the value and set it to zero, then excel doesn't have a fit so to speak.

Thanks to Susannah and Leah for responding. Unfortunately we do not have the option to change the default dates in our data store (for a number of reasons). Leah, I'm not sure what you mean by setting the date to 0. The field format in the data store is HYYMDS, which we display as HMDYY to preserve chronological sorting. So 0 itself is not a valid value. Maybe I'm missing something simple here.

I think what Leah is referring to is that instead of:

PRINT OLD_DATE

do something like (this is just pseudo code, you'll have to use the correct functions for date time fields):

PRINT OLD_DATE NOPRINT
      COMPUTE DISP_DATE/HYYMD=IF OLD_DATE EQ '00010101' THEN '19000101' ELSE OLD_DATE;

Thanks again for the responses. I see that "0" means the SmartDate "0". Unfortunately, the users of our data are accustomed to seeing the 0001/01/01 date as the default in the operational system generating our reporting data. We fear that showing 1900/01/01 in our reports will be confusing to our users. It looks like we may not have an option.

Why not use NULL or missing? Will that sort as per your requirements within Eggshell?

Scratch that as Eggshell has the wonderful base date (just like WF ) but the base date for Eggshell is 01/01/1900 whereas WF has 31/12/1900 (just place 31/12/1899 in a cell and try and format as dd/mm/yyyy!). The reason WF uses 31/12/1900 is to avoid the confusion over 29/02/1900 not being a valid date - place 60 in an eggshell cell and convert to date - MS are wrong and openly admit that fact on their support site. Despite what some folks believe, 1900 was not a leap year.

If you use null or missing then sorting ascending or descending always places the null dates at the bottom of the heap.

If converting to alpha is not an option then I think the "no option" is right.

Confused users? Isn't that a normal state? Have you run the idea past them?

T

This message has been edited. Last edited by: Tony A,

01/01/0001
the original Christmas