duration formula 1

[mosesc]

I have a formula created that displays how long an incident was open. It currently is displayed in seconds. How can I convert this to DD:HH:MM:SS? I have tried example formulas that I found but they are not working.

if {Incident.Status ID:} = "CLOSED" then
DateDiff ( "s", {Incident.Open Date & Time}, {Incident.Close Date & Time})
else
DateDiff ( "s", {Incident.Open Date & Time}, CurrentDateTime )

 

[Ngolem]

Well your formula to get the seconds seems fine...but assign it to a variable for processing.

//@CalcTimeIncidentOpen
WhilePrintingRecords;
numberVar Tdays;
numberVar Thours;
numberVar Tmins;
numberVar Tsecs;
numberVar Temp;
stringVar result;

if {Incident.Status ID:} = "CLOSED" then
Temp := DateDiff ( "s", {Incident.Open Date & Time},{Incident.Close Date & Time})
else
Temp := DateDiff ( "s", {Incident.Open Date & Time}, CurrentDateTime );

Tdays := truncate(Temp/86400,0);
Temp := Temp - (Tdays*86400);
Thours := truncate(Temp/3600,0);
Temp := Temp - (Thours*3600);
Tmins := truncate(temp/60,0);
Tsecs := Temp - (Tins*60);

result := Totext(Tdays,0,"","&quot + ":" +
totext(Thours,0,"","&quot + ":" +
totext(Tmins,0,"","&quot + ":" +
totext(Tsecs,0,"",""

result;




Jim Broadbent

 

[mosesc]

Thanks so much Jim, that did it!

 

[rosemaryl]

Dear Mosesc,

Just a note for you on the field you are using. You are reporting against a MagicTSD database. Using the Status ID field may give you erroneous results.

The Status ID field supports User Defined Statuses which ultimately resolve to a State: of Open or Closed. If your organiation has or will in the future have additional statuses, your formula would be incorrect as you assume that any status that is not Closed is Open... this may or may not be the case. Statuses could be Resolved, Research, On Hold, etc.

The best course of action would be to use the Incident.State: field in your calculation.

Here is the formula I use, which gives the same results as Jim's except I like 2 digits as in 00:00:00:00 as the format:

//Convert Total Seconds to D H M S

WhilePrintingRecords;

Datetimevar d1 := {Incident.Open Date & Time};
Datetimevar d2 := If {Incident.State:} = 'C' then {Incident.Close Date & Time} else CurrentDateTime;

NumberVar TotalSec := datediff("s", d1, d2);
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') + ':'+
Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'')

Best regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.