CODEWITHSUNDEEP
timecrystal-reportsformula
Dagz200
Dagz200

Reputation: 211

Crystal Formula to sum time field in footer

I'm trying to add/sum up TIME and have it in TIME format HH:MM in the footer The field was a number field only showing by minutes (Example: 60.00) and created a formula to convert it to HHMM (01:00) by:

whileprintingrecords;
numberVar hrs;
numberVar min;
stringVar hhmm;
hrs := Remainder(Truncate({field.duration}/60),60);
min := Remainder({field.duration},60);
hhmm := totext(hrs,"00") + ":" + totext(min,"00"); 
hhmm

Now I need to add it in the footer but in format HH:MM any help is greatly appreciated.

Upvotes: 1

Views: 5546

Answers (3)

Mouzam Basheer
Mouzam Basheer

Reputation: 49

Make Two Formula

  1. Create this formula (@time) to convert all the string fields to time and then to seconds:

    timevar t:= time({time_field});
(hour(t)*3600) + (minute(t)*60) + second(t);

  2. Create this formula to add all the seconds and convert them back to hh: mm: ss:

    numbervar fin_t := sum({@time},{groupName}); //@time is the formula you create in the 1st step
NumberVar Hours   := Truncate (fin_t/3600);
NumberVar Minutes := Truncate (Remainder (fin_t,3600)/60);
NumberVar Seconds := Remainder (fin_t,60) ;

    Place it on the Report Footer.

    Totext (Hours, "00") + ':'+ Totext (Minutes, "00") + ':' + Totext(Seconds, "00");

Upvotes: 0

Aikee
Aikee

Reputation: 1

Create formula in call it "FieldToInt" details 

int(fieldname);

Then, create another formula for remaining decimal for minutes "FieldToDec"

({fieldname} - {fieldtoint}) * 100

Finally in group footer add this formula

numbervar hh;
numbervar mm;
numbervar a;
numbervar b;
numbervar c;

hh := sum({FieldToInt},{group}); // sum of hh (in hh.mm)
mm := sum({FieldTodec},{group}); // sum of mm (in hh.mm)

//formula to convert total mm to hh.mm
 a := truncate(mm / 60);
 b := a * 60;
 c := (mm - b) / 100;

//This will be the final total hrs output
hh + (a + c);

Upvotes: 0

Ryan
Ryan

Reputation: 7287

I think you kind of answered your own question. Instead of doing that formula for each record's duration just do it for the sum of the durations.

local numbervar sum_mins := sum([field.duration}); //sum up each record's duration in mins
local numbervar hrs;
local numbervar mins;
hrs := truncate(sum_mins/60); //get whole hours
mins := remainder(sum_mins,60); //get remaining minutes
totext(hrs,'00') + ':' + totext(mins,'00')

Then place this formula in the report's footer.

Upvotes: 2

Related Questions