Hi,

I have a date formatting question - we have date column contains mins and seconds (Ex: 2017/08/07 00:00:00.000 Format-HYYMDs). For Displaying purpose we modified the date format it in to "07-Aug-2017" using HCNVRT and removed the unwanted values using EDIT. If I run the report normally I am getting the output is;
ENGLAND | 2017/08/07 00:00:00.000 | 07-Aug-2017 00:00:00.000 | 07-Aug-2017

But if I run through HTMTABLE why am not getting the exact date value like in normal report(Column DAT_CNV)
ENGLAND | 00:00.0 | 00:00.0 | 7-Aug-17

If I click on the cell am seeing the un formatted column in Excel " 8/7/2017 " instead of " 07-Aug-2017 "

Is there any solution? why it is behaving differently!

TABLE FILE CAR
SUM
     COMPUTE DAT/HYYMDs = &YYMD; AS ''
     COMPUTE DAT_CNV1/A30 	= HCNVRT(DAT, '(HDMtYY-s)', 30, 'A30'); AS ''
     COMPUTE DAT_CNV/A12 	= EDIT(DAT_CNV1,'99999999999$$$$$$$$$$$$$$$$$$$'); AS ''

BY  CAR.ORIGIN.COUNTRY
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT HTMTABLE
ON TABLE SET STYLE *
     UNITS=IN,     SQUEEZE=ON,     ORIENTATION=PORTRAIT,$
TYPE=REPORT,     GRID=OFF,     FONT='ARIAL',     SIZE=9,$
ENDSTYLE
END
SET HTMLFORMTYPE=XLS
-RUN
-HTMLFORM BEGIN
!IBI.FIL.HOLD;
-HTMLFORM END
 

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

I don't understand why you are doing so much transformation when only one command can be used.

TABLE FILE CAR
SUM  COMPUTE DAT/HYYMDs = &YYMD; AS 'DAT'
     COMPUTE DAT_CNV1/A30 = HCNVRT(DAT, '(HDMtYY-s)', 30, 'A30'); AS 'DAT_CNV1'
     COMPUTE DAT_CNV/A12  = EDIT(DAT_CNV1,'99999999999$$$$$$$$$$$$$$$$$$$'); AS 'DAT_CNV'
     COMPUTE DAT/HDMtYY-  = '&YYMD.EVAL'; AS 'Simplified'
BY  CAR.ORIGIN.COUNTRY
WHERE READLIMIT EQ 1;
WHERE RECORDLIMIT EQ 1;
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT HTMTABLE
ON TABLE SET STYLE *
     UNITS=IN,     SQUEEZE=ON,     ORIENTATION=PORTRAIT,$
TYPE=REPORT,     GRID=OFF,     FONT='ARIAL',     SIZE=9,$
ENDSTYLE
END

SET HTMLFORMTYPE=XLS
-RUN
-HTMLFORM BEGIN
!IBI.FIL.HOLD;
-HTMLFORM END

As for Excel it will always have the date format in the cell itself, 07-Aug-2017 is only a display format but to show that way, Excel need the real date format.

For an example am giving as &YYMD. Real time column format is HTTMDS.

If you remove the HTMTABLE and try with "ON TABLE PCHOLD FORMAT EXL2K" we can see the real date format from WebFOCUS to excel,
Only issue is if we use HTMTABLE the real date format is changed in Excel!

Hope now you will understand !

If you do pay attention to both result cell format you will notice some differences.

Using EXL2K or XLSX format the resulting format is "General" and not evaluated as a date since Excel has received a char string (A12 from your fex).

Using the HTMLTABLE with XLS the Excel cell format is : Custom dd-mmm-yy. What I think is that Excel do receive a data and "changing" it into a real date format to be then able to display as the requested custom format.

Try the following.

Generates you data as XLSX or EXL2K and then in Excel select the cell where the data is displayed. Using the bottom right corner of the cell (where the + sign appear) drag it a few cell bellow.
The result is that you will still have "07-Aug-" but the year will be increased. This is a sign that it's not a date.

Do the same but using the HTMLTABLE Excel output. The dragged cell will increase by 1 day from the above cell : meaning that the cell IS a date.

In conclusion, I would say that there is no bug with HTMLTABLE : it send to Excel as a date which is okay, since using Excel directly, it send it as an alpha date string, which is also okay since it's the defined format.

Two different way, tow different result.

Yours to choose

I love it .

I have two different outputs;
1. Normal EXL2k report with more columns.
2. XLS through HTMTABLE format to display the titles & few rows in 90 degree text rotation using CSS. (Like to print the report in A4 landscape)
Both the final outputs column and formats are same; difference is the way we are printing in excel.

As a WebFOCUS team we can say it as a date format in HTMtable. But from business users they will expect the full date format!

so there is no way!

There is always a way and some time many ways :-)

It can be displayed with text month as RealDate in either format or RealTextDate in HTMTable format only.
Personally, I think that both solutions are good depending on your needs until you keep the date as a date.
That way you (your users) still have the flexibility to process date in Excel as they should.

I do prefer the solution for RealDate field since it does work with EXL2K, XLSX and HTMTable, kept as a date and displayed with text month (as you need). So one solution for multiple format.

Solution A

TABLE FILE CAR
SUM  COMPUTE REAL_DATE/HDMtYY- = '&YYMD.EVAL'; AS 'RealDate'
     COMPUTE DAT/HYYMDs = &YYMD; NOPRINT
     COMPUTE DAT_CNV1/A30 	= HCNVRT(DAT, '(HDMtYY-s)', 30, 'A30'); NOPRINT
     COMPUTE DAT_TXT/A12 	= EDIT(DAT_CNV1,'99999999999$$$$$$$$$$$$$$$$$$$'); AS 'RealTextDate'
BY COUNTRY
WHERE READLIMIT EQ 1;
WHERE RECORDLIMIT EQ 1;
ON TABLE HOLD FORMAT HTMTABLE
END

SET HTMLFORMTYPE=XLS
-RUN
-HTMLFORM BEGIN
!IBI.FIL.HOLD;
-HTMLFORM END

Solution B

TABLE FILE CAR
SUM  COMPUTE REAL_DATE/HDMtYY- = '&YYMD.EVAL'; AS 'RealDate'
BY  COUNTRY
WHERE READLIMIT EQ 1;
WHERE RECORDLIMIT EQ 1;
ON TABLE PCHOLD FORMAT XLSX
END

How to show the full year '08-Aug-2017' using HTMTABLE?

I think that falls under MS-Excel setup or you may need to use a Excel macro to change Custom display from "dd-mmm-yy" to "dd-mmm-yyyy".

Since the date is passed as 07-08-2017 to Excel from the HTMTable it is the custom display that has to change, and this (I think) has to be performed into Excel.

Using the output as XLSX instead of HTMTable generates a Custom display of "dd-mmm-yyyy" using RealDate field (not with RealTextDate since it's a character string).

Now it's all a question of choices. Each solution (XLSX or HTMTable) have is pros and cons.
Personally I prefer the XLSX format since it keep it closer to the Excel format and functionalities.
I understand that users may want to have nice colored/formatted layout, but damn it's an Excel file where they will probably plays with numbers and doing their own formulas and styling.

Is it that much important to use the HTMTable to be able to produce an Excel file exactly colored/formatted (and whatever else) as they want ?
This is going to be your battle. Sometime you can't have "the butter and the money for the butter". Choices have to be done.

Thank you MartinY

My pleasure.

If solved, edit your first post then add [SOLVED] at the beginning of the subject.