Calculating time in between intervals 1

[qster]

Hi,
Need some help with calculating time in between the end date (in dd/mm/yyyy hh/mm/ss) and the next start date.
I work in a call center and I'm trying to figure out how to get Crystal to measure this time inbetween.

eg.
START_DATE END_DATE
2001/11/26 06:16:00 2001/11/26 06:37:00
2001/11/28 16:54:00 2001/11/28 16:56:00
2001/12/01 11:44:00 2001/12/01 11:45:00
2001/12/02 23:51:00 2001/12/03 00:00:00
2001/12/07 07:30:00 2001/12/07 07:31:00

I appreciate any input on how or what formula to use to calculate the time in between the End Date of the 1 line to the start_Date of the next.


Thanks in advance

 

[kmcclung]

I too have created reports for call centers and have calculated the time btwn the end date/time and the next start date/time. Here is what I do:

Create a forumula - @SecsBtwnEndStart
//The following formula will calculate the number of seconds btwn the EndDateTime and the next StartDateTime

WhilePrintingRecords;
//get the login date of the next record
DateTimeVar nextLogin := Next({StartDateTime});
//get the Date diff between the logout date of this record and nextLogin
NumberVar myDateDiff := DateDiff("s",{EndDateTime}, nextLogin);

myDateDiff

I supressed this field and created another formula that will convert the seconds to a HH:MM:SS format.

WhilePrintingRecords;
NumberVar TotalSec := {@SecsBtwnEndStart};
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

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

I made the details section a little bigger and placed this formula just below the record info, this displays it btwn each record.

Hope this helps!

 

[dataent]

qster,
Download "ufldtdiv.exe" from

http://support.crystaldecisions.com/downloads

This is a function to calculate the differences between dates.

 

[kmcclung]

I forgot to mention...
If the dates span days, you can replace the last formula I provided, with the one below. It is the same except it will format the secs bwtn the 2 times as DD:HH:MM:SS

WhilePrintingRecords;
NumberVar TotalSec := {@Total Time Btwn Segments};
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,'')

 

[qster]

kmcclung

many thanks.
That solved the time interval, but now I'm facing the problem of the last total gives me a negative time.
do I need to input a variable to tell it to stop?

 

[kmcclung]

You could put a formula in the supress x-2 for the formula field so that if it less than 0, it isn't displayed. Will that work?

left({@Formula}, 1) = "-"

 

[qster]

kmcclung,
didn't seem to work.
other problem I'm facing now is, it keep tabulating the time inbetween intervals for serperate tickets or grouped data.

so I need help on the script to tell it to stop doing the time interval on the next ticket/group.

 

[kmcclung]

Is your formula field a string or a number?

What do you mean by "it keep tabulating the time inbetween intervals for serperate tickets or grouped data"?

 

[qster]

formula is a string.

here's an example:

ticket # Start_date End_Date Time in btwn.
1 13:00:00 14:00:00
15:30:00 15:45:00 01:30:00

2 18:00:00 18:30:00 02:15:00

the problem is, its calculating the last End_date of ticket #1 to the start_date of ticket #2.

What i was asking was, if I needed to tell the formula only to calculate the time interval between end_date and next start_date only within the same ticket #?

 

[kmcclung]

If the formula is a string, the formula:
left({@Formula}, 1) = "-"
should work within the supress condition. If it's not, I can't explain why because it works within my report. I know this may be a stupid question, but you replaced the @Formula above with your formula name?

Give me a minute to look into the other issue. My report is set up differently so there isn't an issue of the last end and next start between groups. But I'll try to figure something out and I'll let you know.

 

[kmcclung]

OK, add this to your conditional supressing of the formula field:

left({@Time Between}, 1) = "-" or
{TicketNum} <> next({TicketNum})

This should supress the field when it is a negative and when the ticket number changes.

Let me know...

 

[qster]

hi,
it gave me a &quot;a formula cannot refer to itself, either directly or indirectly&quot; error.
when i put in:
left({@Time Between}, 1) = &quot;-&quot; or {TicketNum} <> next({TicketNum})

now should I be placing this at the end of the last statement or start a new one?

this is what I have before:

WhilePrintingRecords;
//get the login date of the next record
DateTimeVar nextLogin := Next({ActivityClosed.START_DATE});
//get the Date diff between the logout date of this record and nextLogin
NumberVar myDateDiff := DateDiff(&quot;s&quot;,{ActivityClosed.END_DATE}, nextLogin);

I think I've been at this too long I can't the see obvious error. Should I be using a if statement?

 

[kmcclung]

OK, I see the problem. You need to put:
left({@Time Between}, 1) = &quot;-&quot; or {TicketNum} <> next({TicketNum})


In the conditional supressing formula. Do do this, right-click on the field and select Format Field. Then under the Common tab, there is a Supress check box with a x-2 button to the far right of it. Click that and insert the formula above there. The x-2 button will then display as red (meaning there is a formula for when to supress the field). This should then supress the formula field in your report if it is a negative field or the ticket numbers don't match.

Let me know...
Kathleen

 

[qster]

Kathleen,

Thanks so much. )
It worked!

Quang