Hi,

I'm trying to display a ratio in the excel cell, for example I tried using the following code

TABLE FILE LEDGER
SUM AMOUNT FOR ACCOUNT
1010 AS 'CASH ON HAND'                                    OVER
1020 AS 'DEMAND DEPOSITS'                                 OVER
1030 AS 'TIME DEPOSITS'                                   OVER
RECAP TOTCASH = R1 + R2 + R3; AS 'TOTAL CASH'             OVER  
RECAP TEST1/I3=10; OVER
RECAP TEST2/I3=20; OVER
-*RECAP TEST3/A1=':'; OVER
RECAP TEST4/A50=TRIMV('L',EDIT(TEST1),10,'0',1,'A12') || ':' || TRIMV('L',EDIT(TEST2),10,'0',1,'A12'); 
ON TABLE PCHOLD FORMAT EXL2K
END

It should show as '10:20' instead in the excel output it is showing as 0.492 something like this. I tried hardcoded the colon value to a WF varialble and called when concating and also I tried as a field and concated but still its showing the same. Any solution for this ?

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

Apparently the colon is being interpreted by excel to eman something else than just a colon.
Funny thing is that if you do a HOLD FORMAT EXCEL (not EXL2K) than it is not being interpreted.
You could also choose some other character to separate the two figures, or add soemthing to the string that would prevent interpretation.

Fotr instance, if you do something like:

RECAP TEST4/A50=TRIM('L',EDIT(TEST1),9,'0',1,'A9') || '-:-' || TRIM('L',EDIT(TEST2),9,'0',1,'A9');

and do a hold EXL2K, then it comes out like '10-:-20'.

Perhaps it helps to prepend that string by a " (double-quote) so that Excel knows to interpret that value as literal text?

RECAP TEST4/A51= '"' || TRIMV('L',EDIT(TEST1),10,'0',1,'A12') || ':' || TRIMV('L',EDIT(TEST2),10,'0',1,'A12');

I'm more than a little disappointed that you all didn't figure this out and have an answer waiting for me when I searched.

I'm having this problem, in Version 8, for EXL2K format only, for Recap test fields, that are in subfoots, that contain an hh:mm:ss format. The two colons are turning the text result into a numeric result so I get tiny little numbers that appear as zero in the subfoot.

I'd use EXL07 but we're already dodging another bug that IBI is aware of and hasn't fixed yet. EXL07 works fine in Version 8. EXL2K works fine in Version 7. I'm in the process of upgrading and this is one of my showstoppers.

It's not that your mother and I are angry, it's more that we're disappointed. I'd like you to spend a moment to think about what you've done.



The conversation did get me thinking in that direction though, particularly the "put it on the front of the string" part. I had been trying to put it where the colons were.

Here's what I did to fix this problem, which is more than a little hokey. I'm appending a carriage return character onto the front of the string, as it's one of only two I've found that doesn't show up as a character. ASCII 10 (line feed) used two rows, and even ASCII 7 (bell) placed a character in the field.

AVG_WAIT_FORMATTED_RECAP/A20 = HEXBYT(13, 'A1') | EDIT(AVG_WAIT_HOURS_RECAP, '$$$$$$$99') | ':' | EDIT(AVG_WAIT_MINUTES_RECAP, '$$$$$$$99') | ':' | EDIT(AVG_WAIT_SECONDS_RECAP, '$$$99');

That HEXBYT command on the front of the string convinced Excel to keep the string at text.

This issue isn't Closed anymore, it's FIXED. That's TWO hard bugs in one day! One more hard bug to go!

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

quote:

Originally posted by John_Edwards:
It's not that your mother and I are angry, it's more that we're disappointed. I'd like you to spend a moment to think about what you've done.

Best thing I've read all day!

BTW Excel interprets a tick(') at the beginning of the line as indicating text. Starting the line with that may also have resolved your issue.

"it's more that we're disappointed" hahaha!

eric is correct, just starting the string with an apostrophe would have solved the problem.

Data formatted as xx:xx:xx is being interpreted as a time value. The tiny fraction is the decimal fraction of 1 day the time represents. So, 10:20 is 10:20 am and the .430556... is the fraction of a day that 10:20 am represents. This is being computed as 620 minutes / 1440 minutes per day = .430556...

An apostrophe did not correct the problem for me in EXL2K format, and it adds an apostrophe in the event the user selects PDF format which was a concern in my case. The Carriage Return worked correctly for both.

It's curious. I'm sending text. It just happens to have a 99:99:99 format. Either WF or Excel is leaping to a conclusion that it shouldn't. I never did figure out which.

Jonh the problem in excel is that you don't send a data type with the information to excel. So when Excel gets the output, it decides what formatting it wants to apply according to "Content" of the cell. I believe this happens when you open the file. Thats why tricks like the Carriage Return work. Because now it sees CR content, which to excel is not time. But 99:99:99 in which Excel will do the calculation that JG showed. If you were to change the format of the field to time or whatever, you would see it convert into the expected value.