CODEWITHSUNDEEP
crystal-reports
Relax
Relax

Reputation: 327

How to calculate hours and minutes from decimal number in crystal report

How to calculate hours and mins from decimal number using crystal report formula

Hours and mins

         42.00
        397.00
          2.20
         63.40
          2.36
          1.30
          0.50
    ----------------
        508.76
    ----------------

Sum of values 508.76

I want to convert it into 509.16 -> 509 Hours 16 mins

Explanation In the 76 mins -> 60 mins converted to 1 hr so 509 hrs

remaining 16 mins displayed as mins so 509.16

Achieve using this method: 508 + (76/60) = 509, 76%60 = 16

76/60 Quotient 1, it added to integer part 508 + 1 now 509 hrs

76%6 Remainder 16, it will be 16 mins

tried this code for get fraction value

local numbervar sum_hours := sum({Command.TotalHours});
local numbervar fraction;
fraction := sum_hours - truncate(sum_hours);  
it gives 0.76, how to get 76

can anyone explain with complete code how to write below expression and get result using crystal report formula

split 508.76 into 508 and 76 and calculate using below method
508 + (76/60) = 509, 76%60 = 16

Upvotes: 0

Views: 1228

Answers (2)

R. McMillan
R. McMillan

Reputation: 1422

I think the easiest approach to your problem would be to convert the individual values so the time they represent is expressed in minutes instead of "hours.minutes".

This formula takes a value of 5.38 (5 hours, 38 minutes) and converts it to minutes by splitting the value into a string array using the decimal as a delimiter. It then multiplies the hours by 60 to convert it to minutes, and adds the remaining minutes.

Whileprintingrecords;
Shared NumberVar value:= 5.38;
Shared StringVar array x := split(ToText(value),".");
Shared NumberVar hours := ToNumber(x[1]) * 60;
Shared NumberVar minutes := ToNumber(x[2]);
hours + minutes;

Once you have all of your data converted to minutes, you can easily sum them, then convert it back to hours and minutes with this formula. Replace {@Test} with the fieldname that contains the total value (in minutes) that you want to convert back to "minutes.hours".

WhilePrintingRecords;
ToNumber(ToText(Truncate({@Test}/60),0,"") & "." & ToText({@Test}Mod 60,0,""));

Upvotes: 0

mweber
mweber

Reputation: 688

To transform 0.76 to 76, multiply by 100:

local numbervar sum_hours := 508.76; //sum({Command.TotalHours});
local numbervar fraction;
local numbervar hours;
local numbervar minutes;
fraction := sum_hours - truncate(sum_hours);
hours := truncate(sum_hours) + (fraction mod 60);
minutes := remainder(fraction * 100, 60);

ToText(hours,0) + ' hours and ' + ToText(minutes,0) + ' minutes'

Upvotes: 0

Related Questions