[SOLVED] Multiple date formats in single report column exported to excel

Does anyone know/or could suggest, a way to retain the date format identity of an excel cell; when multiple date formats for a single report column in webfocus have been exported to excel?

The Issue -
I have been able to export multi date formats (‘YY’, ‘MtYY’ ‘DMtYY’) for a single reporting column in WEBFOCUS by dynamically altering the date format at each row cell; example:

TABLE FILE DATEFILE
PRINT
DATEOUTPUT
DATEFORMAT
ON TABLE HOLD AS TEMPDATE FORMAT FOCUS
END
-RUN

TABLE FILE TEMPDATE
PRINT
DATEOUTPUT/DATEFORMAT AS ‘Date’
ON TABLE PCHOLD FORMAT EXL2K
END

The EXL2KTXTDATE is set to ‘VALUE’, but as DATEFORMAT is a defined alphanumeric to hold the three date formats; when the report is exported to Excel the date cells are not identified as a date format and cannot be sorted correctly.

Thanks,
Craig G

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

Webfocus 7611
OS:XP
HTML, AHTML,CSV,EXCEL

Posts: 1 | Location: uk | Registered: February 09, 2011

Kerry

Expert

posted

Hide Post

Hi Craig,

Can you please try the following code from our technicals:

DEFINE FILE EMPDATA
CFORMAT/A8=DECODE DEPT ('SALES' 'YMD' 'MARKETING' 'MtDY'
ELSE 'YMTD');
END
TABLE FILE EMPDATA    
PRINT HIREDATE HIREDATE/CFORMAT
BY DEPT
WHERE DEPT EQ 'SALES' OR 'MARKETING' OR 'PERSONNEL'
ON TABLE PCHOLD FORMAT EXL2K
END

Cheers,

Kerry

Kerry Zhan
Focal Point Moderator
Information Builders, Inc.

Posts: 1948 | Location: New York | Registered: November 16, 2004

George Patton

Master

posted

Hide Post

mmmmmmm.... I like that idea. Neat.

WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP

Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010

Ignored post by George Patton posted

Show Post