Converting Total number of minutes to hours and minutes

[haneen97]

Hi,

I have a total field that has the number of minutes such as (106), I am trying to make a field that would have the number of hours and minutes such as (1.46) instead of (1.77); The 1.77 results from dividing the 106/60.

Please help
Thanks
Mo

 

[bdreed35]

I am sure there are numerous ways of accomplishing this. Here are a couple of possibilities:

Formula 1:

Val(ToText(Truncate({table.minutes}/60),0) + "." + ToText({table.hours} MOD 60))

Another example would be to add the minutes to a date field with with the time portion set 0.

Formula 2:

ToText(DateAdd("n", ({table.minutes}, DateTime (2003, 1, 1, 0, 0, 0)),"H:mm")

The problem with Formula 2 is that it will go back to 0 if your minutes is greater than 1439.

~Brian

 

[MJRBIM]

Another way to skin this cat is a formula that can be displayed as a 24 hour TIME like this...

MINUTES HoursMinutes
- - - - - - - - - - - - - - - - - - - - - - - - - -
106 1:46
126 2:06
74 1:14
15 0:15
- - - - - - - - - - - - - - - - - - - - - - - - - -

Name : {@HoursMinutes}
Formula : Time (Int ({TableName.MINUTES}/60),({TableName.MINUTES}-(Int ({TableName.MINUTES}/60)*60)),0)

 

[haneen97]

MJRBIM's way worked for me because I was formatting a total field not a database field. But when I ran the report I got "Numeric overflow" error. Any suggestions?

Sorry for the late response, I had to go home early yesterday. Thanks for all your help.

Mo

 

[MJRBIM]

Make sure you have the INT in all the places, you only want the INTEGER (ie. whole number = 1) not the decimal (ie. 1.47).

 

[haneen97]

Bdbreed35,

I used the code below you suggested:

Val(ToText(Truncate({table.minutes}/60),0) + "." + ToText({table.hours} MOD 60))

It worked but it has a small bug that I can't fix;

if the total number of hours is less than 60, it displays the number with a decimal point to the left only.

total minutes Total hours
-------------- --------------
30 .30 as opposed to .5.





Mo

 

[bdreed35]

Mo:

So what exactly do you want to see? Can you post what you want to see..

This solution:

Val(ToText(Truncate({table.minutes}/60),0) + "." + ToText({table.minutes} MOD 60))

returns .30 when the minutes equals 30
and
returns 1.46 when the minutes equals 106.

If you want to see an actual fraction when the minutes is under 60 just put some logic into the formula to check for that condition:

if {table.minutes} < 60 then
    {table.minutes} / 60
else
    Val(ToText(Truncate({table.minutes}/60),0) + &quot;.&quot; + ToText({table.minutes}MOD 60));

I don't understand why you would want to do this because it makes your results inconsistent.

~Brian

 

[synapsevampire]

I have a FAQ here which converts seconds to HH:MM:SS format, perhaps that will help you, but I agree with Brian, you seem to have differing requirements:

faq767-3543

-k