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,

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

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??

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"?

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!!