Converting time to minutes and seconds with line graph 1

[ExuroFire]

I am using the formula below to calculate a time and it is returning the results below:

CRtimeFromSecsSinceMidnight(PthPercentile (90, {@travel}, {FCalls_With_Staff.CALL_CREATED_DATE},"monthly"))

12:11:15AM
12:10:53AM
12:10:14AM

The translation of the results is actually 11 min 15 sec, 10 min 53 sec, etc...

I just used the format field function of crystal to change it to custom MM:SS, however it is not retaining that format when I add a line graph. It is graphing seconds (i.e. 675 instead of 11:15)

Is there a time conversion that I can use at the formula editor level instead of the format editor so that my graph will show the minutes / seconds correctly?

 

[Charliy]

try totext({timefield},"MM:SS")

 

[ExuroFire]

So this is what I did and the result that I got.

First from the thread above this - CRtimeFromSecsSinceMidnight(PthPercentile (90, {@travel}, {FCalls_With_Staff.CALL_CREATED_DATE},"monthly")) is a formula field called Monthly Travel. What I did was create a new formula to do the conversion called MonTravTimeConv

totext({@Monthly Travel},"MM:SS")

my output was MM:SS.....as that text not the actual minutes and seconds

 

[Charliy]

you could create a formula that will give you Minuter + (seconds/100)
That will give you 9.45 for 9:45

The graph would have some distortion because the gap between 9.59 and 10.00 would be large even though the difference is a second.

I would just divide the seconds by 60 and plot that. Then 9:45 shows up as 9.75, and the differences will be consistent.

 

[SkipVought]

Hi,

We use "time" to either refer to a point in the arrow of time, like 12 o'clock (according to the clock) or a duration that is the difference between two points on the clock, so to speak.

AP/PM imply some point in time. Midnight is actually 00:00:00. The second before midnight is 23:59:59, using a 24 hour notation.

you're using DURATION.

Co you want the calculated difference between midnight and some future point on the clock, yes? Technically it is...
[tt]
00:11:15 - 00:00:00
[/tt]

 

[BettyJ]

In Charliy's formula above, change MM:SS to mm:ss as follows:
totext({timefield},"mm:ss")

 

[ExuroFire]

Betty that did the trick and it is giving me minutes:seconds like I wanted, however because it is a string field, crystal won't allow it to be a field chosen in the line chart. Only time and numbers apparently. Thoughts / ideas?

 

[SkipVought]

By making it TEXT, the chart plot your "times" as categories, not as numbers, which time is.

Rather plot the time DURATION values and format the axis as 24 hr time

 

[ExuroFire]

Can you elaborate a little more on how I could use Duration here? You see my original formula above that is giving me my correct answer in seconds (time) and I just need it to display on the line chart as mm:ss. The solution I used directly above that is giving me the correct format is not even an allowed selection in the chart data section, even under the advanced tab.

Thanks again.....really trying to get over this hurdle. Haven't had one in months, hence me coming here on a recommendation.

 

[SkipVought]

You don't want seconds: you want DAYS, cuz that's what can format on the axis as mm:ss

 

[ExuroFire]

So I looked up Duration and cannot see how to apply it here since it looks like financial duration


Duration (settlementDate, maturityDate, couponRate, yield, frequency)
Duration (settlementDate, maturityDate, couponRate, yield, frequency, basis)

 

[SkipVought]

All that durarion is, is a difference between two date/time values. Nothing fancy!

 

[ExuroFire]

So I have a modification to this report and need some opinions on how to do this. First my time result that is showing minutes:seconds in the group list correctly, once it shows on the graph it is showing like a number and formatting does not give me a time choice (i.e. - in the list it shows 16:26 but on the graph it shows 16.43.

Secondly they are wanting me to put some time markers on the graph to represent key dates that the system was changed.

Thoughts?

 

[SkipVought]

in the list it shows 16:26 but on the graph it shows 16.43"

???

16 minutes and 26 seconds is 0.011412037 of a day, so I don't know where 16.43 is coming from.

16 minutes and 26 seconds is represented time wise as 00:16:26. However 16:26 is 16 hours and 26 minutes, which is 0.684722222 of a day. That still does not answer the 16.43.

Is your value axis a date/time axis?

 

[ExuroFire]

It appears to be converting the time in the report to a number on the graph. 26 seconds is .43 of a minute (26 is to 60 as .43 is to 100) As far as the axis I cannot see where it lets me change it to time and I don't see why it is doing it by itself.

 

[SkipVought]

Silly of me to have missed that relationship.

My charting experience has been in Excel.