Some translated dates can be sent to Excel 2000/2003 as standard date values with format masks, enabling Excel to use them in functions, formulas, and sort sequences. The SET EXL2KTXTDATE command allows you to specify that translated dates should be sent as date values with format masks instead of text values.

SET EXL2KTXTDATE = {TEXT|VALUE}

SET EXL2KTXTDATE=TEXT
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/01/2010';
WRMtrDY/WRMtrDY = NEWDATE;
wDMTY/wDMTY = NEWDATE;
wrDMTRY/wrDMTRY = NEWDATE;
wrYMtrD/wrYMtrD = NEWDATE;
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE WRMtrDY wDMTY wrDMTRY wrYMtrD
ON TABLE HOLD FORMAT EXL2K
END

Date formats that do not specify the day value explicitly are defined as the date value of the first day of the month. Therefore, the value placed in the cell may be different from the day component value in the source data field and may produce unexpected results when used for sorting or date calculations in an Excel formula.

The following table shows how FOCUS date formats are represented in EXL2K. The table shows how the value is preserved in the cell and how the display is generated using the format mask that corresponds to the FOCUS date format.

DATEFLD/MDYY = '01/02/2010'

FOCUS Format	SET EXL2KTXTDATE=TEXT		SET EXL2KTXTDATE=VALUE
	EXL2K Displays:	EXL2K Value:	EXL2K Displays:	EXL2K Value:
DMYY	02/01/2010	1/2/2010	02/01/2010	1/2/2010
MY	01/10	1/1/2010	01/10	1/1/2010
MTY	JAN, 10	JAN, 10	Jan, 10	1/1/2010
MTDY	JAN 2, 10	JAN 2, 10	Jan 2, 10	1/2/2010

The following request against the GGSALES data source creates the date January 2, 2010 and passes it to Excel 2000/2003 with formats MDYY, DMYY, MY, and MTDY:

SET EXL2KTXTDATE=TEXT
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/02/2010';
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE AS 'MDYY' NEWDATE/DMYY AS 'DMYY' NEWDATE/MY AS 'MY' 
        NEWDATE/MTY AS 'MTY' NEWDATE/MTDY AS 'MTDY'
ON TABLE HOLD FORMAT EXL2K 
END

With EXL2KTXTDATE=TEXT, columns D and E have text values, not date values. The values are displayed in uppercase, as specified by the FOCUS formats (MTY and MTDY):

With EXL2KTXTDATE=VALUE, columns D and E have actual date values with format masks, displayed by Excel 2000 in mixed-case. Since the MTY format does not have a day component, the date value stored is the first of January 2010 (1/1/2010), not the second of January 2010 (1/2/2010):

Dates formatted as individual components (for example, D, Y, M, W) are passed to Excel as numeric values that can be used as parameters to Excel date functions. The values are passed as general text format that are recognized by Excel as numbers. These values are passed to Excel in the same format regardless of the setting for EXL2KTXTDATE.

The following request against the GGSALES data source creates the date January 1, 2010 and extracts numeric date components, passing them to Excel 2000:

SET EXL2KTXTDATE=VALUE
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/01/2010';
D/D = NEWDATE;
Y/Y = NEWDATE;
W/W = NEWDATE;
w/w = NEWDATE;
M/M = NEWDATE;
YY/YY = NEWDATE;
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE D Y W w M YY
ON TABLE HOLD FORMAT EXL2K 
END

With SET EXL2KTXTDATE=VALUE, the output is:

Date formats that contain a Quarter component are always passed to Excel as text strings since Excel does not support Quarter formats.

The following request against the GGSALES data source creates the date January 1, 2010 and converts it to date formats that contain a Quarter component:

SET EXL2KTXTDATE=VALUE
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/01/2010';
Q/Q = NEWDATE;
QY/QY = NEWDATE;
YBQ/YBQ = NEWDATE;
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE Q QY YBQ
ON TABLE HOLD FORMAT EXL2K 
END

Even with SET EXL2KTXTDATE=VALUE, in Excel 2000/2003 the cells containing dates with Quarter components have General format. To see this, open the Format Cells dialog box.

The output is:

Date formats that do not contain sufficient information to present the valid date result in Excel are not translated to a value, including formats that do not contain year and/or month information. These dates will continue to be sent to Excel as text regardless of the SET EXL2KTXTDATE setting. In the absence of complete information, the year defaults to the current year, so the value sent would be incorrect if this type of format was passed as a date value. The following formats will not be sent as values:

• MT, MTR, Mt, Mtr
• W, w, WR, wr

Note that since these values are always sent as text, the case defined in the FOCUS format is applied in the resulting cell.

The following request against the GGSALES data source creates the date January 1, 2010 and converts it to date formats that are defined as either month name or day name:

SET EXL2KTXTDATE=VALUE
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/01/2010';
MT/MT = NEWDATE;
MTR/MTR = NEWDATE;
Mtr/Mtr = NEWDATE;
WR/WR = NEWDATE;
wr/wr = NEWDATE;
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE MT MTR Mtr WR wr
ON TABLE HOLD FORMAT EXL2K 
END

In Excel 2000/2003, the cells containing the days have General format. To see this, open the Format Cells dialog box.

The output is: