I have a report that outputs to EXL2K but the fields that are HYYMDS (datetime fields) have a general format in Excel. Is there a way to make this as a datetime field?

I checked the forum and I only saw examples of dates not datetimes conversion in Excel.

Thanks for your help.

Dan

This message has been edited. Last edited by: dballest,

Dan :
I believe you can get around that issue my making sure the field is defined as a character field before it is passed on to Excel.

If you are using SQL passthru, you can --
CHAR(field-name)
If you are using FOCUS code, you can --
COMPUTE/DEFINE using EDIT(fieldname)

Sandeep Mamidenna

Thanks for the reply Sandeep.

It did not work for me. It's still showing as General Format in Excel. Below is the sample code:

TABLE FILE RV_DMRHOLDS_ETL
PRINT
COMPUTE ODATE1/A20=HCNVRT(ORIGINATIONDATE, '(HYYMDS)', 20, 'A20');
ORIGINATIONDATE/HMDYYS
WHERE RECORDLIMIT EQ 5
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT EXL2K
END

I tried the fex below but the date field is not coming up as a date in Excel 2003. Any suggestions appreciated. Thanks.

-SET &TDATE = '05/12/2005 16:45';

DEFINE FILE CAR
TDATE1/A20 = '&TDATE.EVAL';
TESTDATE/HYYMDS=HINPUT(20, TDATE1, 10, TESTDATE);
END

TABLE FILE CAR
PRINT
CAR
TDATE1
TESTDATE
ON TABLE PCHOLD FORMAT EXL2K
END

-EXIT

I have not yet succeeded in passing a date-time format to Excel. In the example below, only the date-formatted fields have a non-general Excel format.

DEFINE FILE CAR
DATETIME/HYYMDS = HINPUT(20, '2005/12/29 16:45.000', 10, DATETIME);
STARTDATE/YYMD  = HDATE(DATETIME, 'YYMD');
END
-RUN

TABLE FILE CAR
PRINT
STARTDATE AS SD
STARTDATE/A8DMYY AS SD1
STARTDATE/A8MDYY AS SD2
STARTDATE/MTDYY AS SD3
STARTDATE/MTRDYY AS SD4
STARTDATE/MtDYY AS SD5
STARTDATE/MtrDYY AS SD6
STARTDATE/MtrYY AS SD7
STARTDATE/QYY AS SD8
STARTDATE/YY AS SD9
STARTDATE/wMtDYY AS SDA

COMPUTE SD1C/A8DMYY = STARTDATE;
COMPUTE SD2C/A8MDYY = STARTDATE;
COMPUTE SD3C/MTDYY = STARTDATE;
COMPUTE SD4C/MTRDYY = STARTDATE;
COMPUTE SD5C/MtDYY = STARTDATE;
COMPUTE SD6C/MtrDYY = STARTDATE;
COMPUTE SD7C/MtrYY = STARTDATE;
COMPUTE SD8C/QYY = STARTDATE;
COMPUTE SD9C/YY = STARTDATE;
COMPUTE SDAC/wMtDYY = STARTDATE;

DATETIME AS DT
DATETIME/HDMYYS AS DT1
DATETIME/HHIA AS DT2
DATETIME/HHIS AS DT3
DATETIME/HHISa AS DT4
DATETIME/HHIa AS DT5
DATETIME/HM AS DT6
DATETIME/HMDYYS AS DT7
DATETIME/HMTDYYSA AS DT8
DATETIME/HMtDYY AS DT9
DATETIME/HMtDYYSA AS DTA
DATETIME/HMtYY AS DTB
DATETIME/HYM AS DTC
DATETIME/HYMD AS DTD
DATETIME/HYYM AS DTE
DATETIME/HYYMD AS DTF
DATETIME/HYYMDSA AS DTG

COMPUTE DT1C/HDMYYS = DATETIME;
COMPUTE DT2C/HHIA = DATETIME;
COMPUTE DT3C/HHIS = DATETIME;
COMPUTE DT4C/HHISa = DATETIME;
COMPUTE DT5C/HHIa = DATETIME;
COMPUTE DT6C/HM = DATETIME;
COMPUTE DT7C/HMDYYS = DATETIME;
COMPUTE DT8C/HMTDYYSA = DATETIME;
COMPUTE DT9C/HMtDYY = DATETIME;
COMPUTE DTAC/HMtDYYSA = DATETIME;
COMPUTE DTBC/HMtYY = DATETIME;
COMPUTE DTCC/HYM = DATETIME;
COMPUTE DTDC/HYMD = DATETIME;
COMPUTE DTEC/HYYM = DATETIME;
COMPUTE DTFC/HYYMD = DATETIME;
COMPUTE DTGC/HYYMDSA = DATETIME;

BY COUNTRY

WHERE COUNTRY EQ 'ENGLAND'

ON TABLE PCHOLD FORMAT EXL2K
END
-RUN

Francis,

Thanks for the reply. I guess I'll open a case and see if this functionality is anywhere near the horizon.

Dan

I just ran Francis' sample fex. The datetime fields are not in actual excel date/time format, but they displayed properly. For example DTGC displays as "2005/12/29 4:45:00PM". Excel sees it as general text, not a date/time field. I assume the problem is that, while Excel displays the field properly, Excel sees it at general text instead of date/time.

Just for the record, Excel see the QYY as general text and the YY as a zip code, all of the others, Excel sees as date fields.

That is correct jgelona. The customer has a Excel application with macro (vb scripts). It's expecting the cell to be a date but it is recognized as general text.

Opened a case with IBI and it's been on their plate for awhile (NFR) and not sure when it will be out.

In the meantime, the macro code had to be changed to add an intermediate step to convert the cell to a data-time type.

Dan

Reference: Usage Notes for Date and Numeric Formats

The following formats are not supported in EXL2K.
They will translate into Excel General format and possibly produce unpredictable results:

YY, Y, M, D, JUL, and I2MT.

Any date format with a Q (quarter).

Any packed-decimal (P) date formats.

Any alphanumeric (A) date formats.

Fixed Dollar (N) formats.

Multiple format options.
Only single format options are supported when using FORMAT EXL2K.
For example, the formats I9C and I9B are supported, but I9BC is not.

The following applies to headings and footings with embedded numeric fields:

If you embed a numeric field in a heading, subheading, footing, or subfooting of a report in Excel 2000 or higher format,
the numeric field displays in Excel general format (text).

To display a numeric field in Excel number format, you must set HEADALIGN=BODY in the StyleSheet.



Dev studio on-line help

EXL2K display format and formatted dates

This message has been edited. Last edited by: <JG>,

I just tried:

SUM
     MIN.DET_FIRSTDETECTDATETIME/HYYMDS
     MAX.DET_LASTDETECTDATETIME/HYYMDS
BY SITE
BY TAGCODE
ON TABLE PCHOLD FORMAT EXL2K
END

with output to EXL2K and was able to do perform date math in Excel without reformatting the date columns. I subtracted LastDetectDateTime (D1) from FirstDetectDateTime (C1)and got the correct answer (in hours).

At least no one suggested using templates and saved the OP from having to reply that those wouldn't really work in his situation.

- ABT

That's what the documentation says.

(late on the submit button - refers to previous post)

Thanks to those who replied.

To M Meagher, did you get the HYYMDS field to appear as a non-general type in Excel using the posted CAR fex above?

Thanks,
Dan

7.7.0.1 format is shown as being general

It stays in 'General' format. Interestingly, Excel treats the columns as if they were date/time format if you use them in a date function. I also tried 'ON TABLE PCHOLD FORMAT EXL07'. It also created the columns as 'General'.

Did anyone find the solution for date formate in Excel? I know it is possible to bring in custom, text and general formate but not in Date itself. did this problem get resolved?

Arif