Time field (UTC) Sum won't exceed 23 hours

[JULY1]

I have been using the following formula and am unable to get a sum that exceeds at total of 23:59.

Sum ({CallDetailRecord.duration}) + Time (00,00,00)

What I want is the Sum to display the total duration of time spent on the phone by a department during a specified month. The total hours must be able to exceed 23. Currently everything seems fine until it exceeds the 23:59 then it goes back to zero and displays only the remainder. How can I get it to display correctly?

FYI: This is a Time field only. All Date/Time fields within this database are in a time_t value that is obtained from the Windows NT (2000) system routines. The value is a coordinated universal time (UTC) value and represents the number of seconds since Midnight (00:00:00) Jan. 1, 1970.


Thank You very much,
Linda

 

[AndrewBaines]

It's because if you add 1 minute to 23:59, you're back to 00:00.
Try two formulae:
@Late
time(23,59,00)

and
@Later
{@late} + 61

@Later will display as 00:00:01
You need to keep the call duration in seconds, then use a formula to calculate hours, minutes & seconds.
Andrew Baines
Chase International

 

[Naith]

Linda,

Andrew's pointed you in the right direction there.

You need to convert your time fields into seconds, so create a formula like:
//********************************************
local numbervar hours;
local numbervar minutes;
local numbervar seconds;

//convert the hours to seconds by multiplying by 3600
hours := hour({YourTimeField1}) * 3600;
//convert the minutes to seconds by multiplying by 60
minutes := minute({YourTimeField1}) * 60;
seconds := second({YourTimeField1});

//add up all the seconds
hours + minutes + seconds;
//****************************************

(For my purposes, I created two time fields, and converted them both to seconds. They are referred to as {@Time Seconds 1} and {@...2} in the following formula)

Then create another formula along the lines of:
//****************************************
local numbervar RemainingSeconds;
local numbervar Hours ;
local numbervar Minutes;
local numbervar Seconds;

timevar hanch1;
//divide the @TotalSeconds by 3600 to calculate hours. Use truncate to remove the decimal portion.
Hours := truncate({@Total Seconds1} / 3600) + truncate({@Total Seconds2} / 3600);
//
RemainingSeconds := {@Total Seconds1}+{@Total Seconds2} - (Hours * 3600);//subtract the hours portion to get //RemainingSeconds
//Divide RemainingSeconds by 60 to get minutes. Use truncate to remove the decimal portion.
Minutes := truncate(RemainingSeconds/60);
//subtract the Hours and Minutes and what is left over is seconds.
Seconds := ({@Total seconds1}+{@Total Seconds2}) - (Hours * 3600) - (Minutes * 60);

//format the hours, minutes, and seconds to hh:mm:ss
totext(Hours,"00&quot + ":" + totext(Minutes,"00&quot + ":" + totext(Seconds,"00&quot
//**********************************************

So, if the value assigned to {@Total seconds1} was 11:00pm and {@Total Seconds2} was 10:00am, the value returned by the last formula would be 33:00:00.

Hope this helps.

Naith

 

[Naith]

Whoops. I left a couple of timevar variables in the code. Ignore them. I was trying something fancy beforehand which ended up messing me up.

Naith Hanchard

 

[JULY1]

This question was reworded and solved. To see how check: Formatted "elapsed time" string - no days, increase hrs answered by Ken Hamady.