Convert Number to Hours and Minutes format

[priley]

Hi

I am new to Crystal Reports 10 and I hope that somebody can help me with this.

Basically I am trying to convert a number to hours and minutes format.

What the guys here do is enter the time spent doing a particular job into Heat. This is not in time format, but simply a number. This can be 20, or 30 or whatever. To Heat, all it is is a number but we all know it means minutes.
The field is known as {Calllog.TimeSpent}

I then run a report that displays all calls logged for a particular group with the sum of {Calllog.TimeSpent} at the end to give the Total Time Spent on that group. However this number ends up being something like 2545. I could just divide by 60, but the rounding doesn't work the way I would like.

Is there a way that Crystal reports can take the sum of this field and format so that I get this kind of result:

The sum of the field is: 245
Formatted sum of field: 4 Hrs, 5 Mins

Thanks a lot
Phil

[peeverocket]

Hi Phil,

Hope this helps....

You have an object that returns a numeric value for minutes. You want to convert this into hours and minutes in a BusinessObjects report. For example, "243 Minutes" should become 4 hours and 3 minutes (4.03).



Resolution
*****CONFIGURATION******

BusinessObject version 4.1.x and 5.x


**********RESOLUTION******

To convert an object called <Original Value> that returns the value 243 to 4.03 follow the steps below:


1. Create a new variable called <Original Value /100> with the formula:

=<Original Value>/100

This will return the value 2.43 in our example.


2. Create a new variable called <Original Value /100 & 0.60> with the formula:

=<Original Value /100>/0.60.

This will return the value 4.05.


3. Create a new variable called <Truncated Div/0.60 (=Hours)> with the formula:

=Truncate(<Original Value /100 & 0.60> ,0).

This will return the value 4.00 and will be the Hour value at the end.


4. Create a variable called <Truncated Value * 0.60>. with the formula:

=<Truncated Div/0.60 (=Hours)>*0.60

This will return the value 2.40.


5. Create a variable called <Remainder (=Minutes)> with formula:

=<Original Value /100>-<Truncated Value * 0.60>

This will return the value 0.03.


6. Set up the entire calculation by creating a new variable called <Time Calculation> with the formula:

=<Truncated Div/0.60 (=Hours)>+<Remainder (=Minutes)>


This will give the correct conversion of 243 to 4.03 and will successfully convert any numeric Minutes value into the correct Hours/Minutes format.

[JaganEllis]

Seems like a lot of effort :)

numbervar a := 245;
numbervar hours := truncate(a / 60);
numbervar mins := a mod 60;
totext(hours, 0, '') + ' hours, ' + totext(mins, 0, '') + ' mins'

[priley]

Thank you very much guys, both methods worked perfectly when tweaked to suit my report.

I'm slowly starting to get my head around it all.

Thanks again!!!

Phil