Joined: 13 Nov 2009
Location: United States
Online Status: Offline
Posts: 9
Topic: Converting a Number to HH:MM Posted: 13 Nov 2009 at 1:03pm
Hello,
I know there are many posts for number to Hours and Minutes but mine is a bit unique and none of the posts so far has solved my issue.
We have a field called {Task_Time} which is a string field and captures the minutes worked on a task for a case. I had to create a formula to make the report recognize the field as a number field. I called it @Time . I need to display the data in HH:MM format and be able to sum as well as get the average on a group level.
I tried the following:
numbervar a := {@Time}; numbervar hours := truncate(a/60); numbervar mins := a mod 60;
But instead of showing 5 minutes as 00:05 or even .05, it shows it as 5.00
Is there anything in the above formula that I am missing?
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 13 Nov 2009 at 2:02pm
You will not want to use the time value for any calculations. YOu will want to use your original @Time numeric formula. You cannot SUm or average a HH:mm value. You will do your calcs on the @Time and use the other field to display those results either at a row level or any aggregate level. YOu will need a different formula per level you want to display at. Below is a sample for your report footer level (total of all values)
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
Posted: 13 Nov 2009 at 2:20pm
I'm sure there are much more elegant solutions but maybe this will work for you.
Global NumberVar StTime; Global NumberVar StHours; Global NumberVar StMin; StTime := 85; StHours := Truncate(StTime/60); StMin:=StTime Mod 60; Right("0" + ToText(StHours,0),2) + ":" + Right("0" + ToText(StMin,0),2)
You could use the last line to display each time. To display a total, you could sum your time (since it is in minutes) and use this logic to display the total.
Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
Posted: 16 Nov 2009 at 7:02am
You would create a new formula that looks like the other one but use the sum of the times in place of the variable called sttime. Use this formula in your report where you need to display the total time.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
