Converting a ToText Time to true date/time/number

[Maven4Champ]

Greetings,

I have a report that has the following calculation called TTCM_TA:

NumberVar avgmins := Sum({TABLE.TTCM}, {TABLE.CODE})
/ Count({TABLE.DEALS}, {TABLE.CODE});
NumberVar dys := avgmins \ 1440;
NumberVar hrs := (avgmins mod 1440)\ 60;
NumberVar mins := avgmins mod 60;

ToText (dys, 00) & ":" & ToText(hrs, 00) & ":" & Right("00" & ToText(mins, 0), 2);

----------------------

My result is as follows (i.e. 41:16:45 - 41 days, 16 hrs, 45 mins).

However, when saving report as excel, it comes in as 41:16:45 and this is un-chartable data format. I have tried every cell formatting in Excel and there is no hope there. My hope is that I could convert it into something in Crystal using a formula conversion and then pass that to Excel to graph. Has anyone run into this problem and know how to resolve it?

It would be easier if Crystal could graph the results themselves but unfortunately it cannot graph a formula/calculation from what I have seen.

Using Crystal 8.5 and an Oracle database and Excel 2000.

Thanks!

 

[lbass]

You should be charting on the actual calculation:

Sum({TABLE.TTCM}, {TABLE.CODE})
/ Count({TABLE.DEALS}, {TABLE.CODE})

Use the earlier formula just for display. I wonder whether:

average({table.TTCM},{table.code})

...would give you the correct figures? You could just chart in CR using a summary: average of {table.ttcm} with an "on change of" field: {table.code}.

-LB

 

[Maven4Champ]

lbass,

This gives me the raw minutes. How would I then convert that in EXCEL to display DD:HH:MM or format that in Crystal to chart?

 

[lbass]

You won't be able to chart on anything but the "raw minutes", since the chart needs to be based on a number, not a string--as far as I know.

-LB

 

[Maven4Champ]

lbass,

I am able to chart in Excel if I manually type in the value of "41:16:45". When looking at the =Value in Excel, the true value that is returning is "1/1/1900 10:16:54 AM". With that said, could I chart the result as a date and then convert it to time of dd:hh:mm in EXCEL.

Let me know if that is possible in Crystal?

 

[lbass]

But it is NOT a date, it is either a string (with your formatting) or it is a number.

-LB