Crystal 8.5 - Need help modifying date/time formula 1

[christinetjx]

I would like to display the amt of time passed between a call being opened, then closed.

I would like this displayed in days, hrs, minutes: (i.e.,
2 days, 3 hrs 10 mins)

i found the (wonderful!) formula posted in faq767-3543, so i'm halfway there; i just need help fine-tuning. Below is that formula with my modifications. Can someone pls help me edit this so i can see the days hrs & minutes. As always, thank you so much for your help.

Christine

The formula I am using:

numberVar dur := datediff("s",{probsummarym1.open_time}, {probsummarym1.close_time}); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

 

[bdreed35]

Change the second to last line to this:

hhmmss := totext(hrs, "0") + " day(s), " + totext(min, "00") + " hr(s), & " + totext(sec, "00") " min(s);

~Brian

 

[lbass]

Try:

numberVar dur := datediff("s",{probsummarym1.open_time}, {probsummarym1.close_time});
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmm;

days := Truncate(dur/86400);
hrs := truncate(remainder(dur,86400)/3600);
min := round(remainder(dur/60,60));

ddhhmm := totext(days,"0")+" days, " +totext(hrs, "0") + " hours, " + totext(min, "0") + " mins";

ddhhmm

-LB

 

[christinetjx]

Thanks to both of you for the quick response. The formula works perfect for calls that are closed. But I need to include the same kind of information for calls still open (sorry, I forgot to mention that detail earlier).
i *think* the formula would say something like this:

if {probsummarym1.status} <> "closed" then
CurrentDateTime-{probsummarym1.open_time}

Is that correct? And where do i place it in context w/the rest of this forumula below:

numberVar dur := datediff("s",{probsummarym1.open_time}, {probsummarym1.close_time});
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmm;

days := Truncate(dur/86400);
hrs := truncate(remainder(dur,86400)/3600);
min := round(remainder(dur/60,60));

ddhhmm := totext(days,"0")+" days, " +totext(hrs, "0") + " hours, " + totext(min, "0") + " mins";

ddhhmm

Thank you again for your patience and time.

Christine

 

[kskid]

modify the formula

numberVar dur; 
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmm;

[b]if {probsummarym1.status} <> "closed" then
  dur := datediff("s",{probsummarym1.open_time}, {probsummarym1.close_time})
else
  dur := datediff("s",{probsummarym1.open_time}, currentdatetime);[/b]

days := Truncate(dur/86400);
hrs := truncate(remainder(dur,86400)/3600);
min := round(remainder(dur/60,60));

ddhhmm := totext(days,"0")+" days, " +totext(hrs, "0") + " hours, " + totext(min, "0") + " mins";

Cheers,
-LW

 

[kskid]

Ooops...

Change that to

if {probsummarym1.status} [red]=[/red] "closed" then
dur := datediff("s",{probsummarym1.open_time}, {probsummarym1.close_time})
else
dur := datediff("s",{probsummarym1.open_time}, currentdatetime);

 

[christinetjx]

Thank you, but the formula is not working correctly.
Calls not closed are still displaying w/no timeframe and the timeframe is off on calls that *are* closed, i.e.

Call#: Status: Opened: Closed: Duration:

123 closed 2/20 22:23 2/20 22:23 1 day, 12 hrs, 7 min

456 updated 2/21 9:41

 

[lbass]

I would change the "dur" variable to:

numbervar dur := if isnull({probsummarym1.close_time}) then
datediff("s",{probsummarym1.open_time},currentdatetime) else
datediff("s",{probsummarym1.open_time},{probsummarym1.close_time})

-LB

 

[christinetjx]

When I use the corrected formula from kskid, it seems to work (thank you!). LBass, when i use your last suggestion (just to compare), i get the error 'remaining text does not appear to be part of the formula'. I probably have it in the wrong place....

a. What is the difference between your two suggestions?
b. Below is the formula (with lbass suggestion) that gives me the error. Can you show me where it is wrong? Thank you!

Christine

numberVar dur;
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmm;

numbervar dur := if isnull({probsummarym1.close_time}) then
datediff("s",{probsummarym1.open_time},currentdatetime) else
datediff("s",{probsummarym1.open_time},{probsummarym1.close_time})

days := Truncate(dur/86400);
hrs := truncate(remainder(dur,86400)/3600);
min := round(remainder(dur/60,60));

ddhhmm := totext(days,"0")+" days, " +totext(hrs, "0") + " hours, " + totext(min, "0") + " mins";

 

[lbass]

You need to add a semicolon as in:

numbervar dur := if isnull({probsummarym1.close_time}) then
datediff("s",{probsummarym1.open_time},currentdatetime) else
datediff("s",{probsummarym1.open_time},{probsummarym1.close_time});

This would work if you actually have nulls. To be safe, you could use:

numbervar dur := if isnull({probsummarym1.close_time}) or
{probsummarym1.status} <> "Closed" then
datediff("s",{probsummarym1.open_time},currentdatetime) else
datediff("s",{probsummarym1.open_time},{probsummarym1.close_time});

-LB

 

[christinetjx]

you guys are a wonderful. thank you thank you thank you.