Time Elapsed between Received Time and Closed Time 2

[JacksonWu]

I'm trying to create a report that will include the time elapsed since receiving a ticket and it's being closed.


The formula I'm using is:

DateDiff ("n", DateTime (Hfwdatetodate({CallLog.RecvdDate}),Time({CallLog.RecvdTime})), DateTime (Hfwdatetodate({CallLog.ClosedDate}),Time({CallLog.ClosedTime})))

The problem I'm encountering is that some of the tickets are not closed, so no closed time exists and the report won't run.

I'm assuming I need to add isnull into my forumla but I can't seem to figure it out.

 

[kray4660]

if isnull({CallLog.ClosedTime}) then

?
else
DateDiff ("n", DateTime (Hfwdatetodate({CallLog.RecvdDate}),Time({CallLog.RecvdTime})), DateTime (Hfwdatetodate({CallLog.ClosedDate}),Time({CallLog.ClosedTime})))

Not sure what you want to do if the closed time is null.

 

[JacksonWu]

Ideally, if the closed time is Null, I'd like it to say something like "Ticket Still Open" or the like, but it seems that won't work because it's a string.

 

[lbass]

if isnull({CallLog.ClosedTime}) then
datetime(0,0,0,0,0,0) else
DateDiff ("n", DateTime (Hfwdatetodate({CallLog.RecvdDate}),Time({CallLog.RecvdTime})), DateTime (Hfwdatetodate({CallLog.ClosedDate}),Time({CallLog.ClosedTime})))

Then you can format the formula by right clicking on it->format field->display string:

if currentfieldvalue = datetime(0,0,0,0,0,0) then
"Ticket Still Open" else
totext(currentfieldvalue,"MM/dd/yyyy hh:mm:ss")

Or change the datetime format as you wish.

-LB

 

[JacksonWu]

lbass:
I tried this and I got "A date-time is required here."

 

[lbass]

Oh, sorry, I wasn't paying close enough attention. You should use:

if isnull({CallLog.ClosedTime}) then
0 else
DateDiff ("n", DateTime (Hfwdatetodate({CallLog.RecvdDate}),Time({CallLog.RecvdTime})), DateTime (Hfwdatetodate({CallLog.ClosedDate}),Time({CallLog.ClosedTime})))

Then you can format the formula by right clicking on it->format field->display string:

if currentfieldvalue = 0 then
"Ticket Still Open" else
totext(currentfieldvalue,0,"")

-LB

 

[JacksonWu]

Using that formula, I was able to save it, but when I run the report, I get the same error.

 

[JacksonWu]

 

[JacksonWu]

I tried simplifying the formula to see if I could get the report to run at all and I'm still getting the same problem.

No errors when saving the formula but it'll give me the "Bad Time Format String" error.

I'm using the following formula

if isnull({CallLog.ClosedTime}) then
Time(0,0,0) else
Time({CallLog.ClosedTime})

 

[lbass]

I think the issue must be with the conversion of Heat date and time fields to the right format for Crystal. The following link might help. I'm wondering whether the time fields needs some kind of conversion. Not sure.

http://support.frontrange.com/support/crystal/HFWFunctions.htm

Why don't you also post back with how the time field displays if placed in the detail section. Please show for hours after 6pm.

-LB

 

[JacksonWu]

Time Fields display like this:
After 6pm:
18:03:21

 

[JacksonWu]

After looking through the Frontrange Crystal Reports: HFW Functions, it looks like they don't have anything like HFWDateToDate for time.

They do have HFWDurHoursNormal which would give me what I was looking for originally which is the time elapsed since the opening and closing of the ticket.

 Example: HFWDurHoursNormal({CallLog.RecvdDate},

{CallLog.RecvdTime},{CallLog.ClosedDate},

{CallLog.ClosedTime})

Sample Input: '1998-02-10','13:00:00','1998-02-12','14:25:00'

Sample Output: 2days 1hour 25mins

Unfortunately, I was hoping to find the average time as well, which I can't use because of the type of output it is in.

 

[JacksonWu]

OK. Nevermind again.

Found an article on how to find the average from there:

http://support.frontrange.com/support/crystal/making_a_duration_formula.htm

lbass : Thank you very much for your help here.