cancel
Showing results for 
Search instead for 
Did you mean: 

DateTime converting into hrs

Former Member
0 Kudos

Hi.

What is the sytanx used when calculating the avg time taken to resolve a ticket?

I have the Arrival Date /Time and Resolved Date/Time field . This is how I used but the results seems to incorrect.

Say if I have ArrivalTime(7/1/2009 5:25:15PM) and ResolvedTime (7/1/2009 7:19:58PM), the formula that I used is

(ResolvedTime - ArrivalTime) , but the calculation is wrong. I am missing something out.

Your help on this is greatly appreciated.

Thanks

Cauvery

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Cauvery,

Before doing the negation just check whether it is datetime field


if (isdatetime(ArrivalTime) and isdatetime(ResolvedTime)) then
     ResolvedTime - ArrivalTime
else
     "This is not a datetime field"

Otherwise, if you want to find the number of days between these two dates then use


datediff("d", ResolvedTime, ArrivalTime)

You can use any of these Interval Types according to your needs


yyyy -  Year
q -  Quarter 
m -  Month
y - Day of year
d - Day (both "y" and "d" find the difference in days)
w - Number of weeks between startDateTime and endDateTime
ww - Number of firstDayOfWeek's between startDateTime and endDateTime
h - Hour
n - Minute
s - Second

Hope this helps!

Former Member
0 Kudos

Hi Ananth,

Thanks for that reply, but this is not what I am looking at.

OK, lets just look at the sample datetime that I have with me and its a datetime field for your information.

Say my Arrival DateTime i.e 7/1/2009 5:25:15PM and my Resolved DateTime is 7/1/2009 7:19:58PM, so accoridng to the SLA calculation, the time taken to resolve a ticket would be in simple maths('Resolved DateTime - Arrival DateTime), and the acutal output should be (1:54:43), but my crystal syntax that you provided is not working. I have a no.of incidents and I also need to take an average from the output produced. Hope now this is clear.

Regards

Cauvery

Former Member
0 Kudos

Hi Cauvery,

Sorry i am not aware of this.

But i have one suggestion that this could be achieved though DB Query.

Former Member
0 Kudos

Hello,

Can someone help me out on this please? Need them very badly.

Regards

Cauvery

Former Member
0 Kudos

Hi Cauvery,

if your DateTime diffrence should not exeed 24 hours(I day), you will get result in Time() format itself. with the following solution.

numbervar tsecs := datediff("s",{StartDateTime},{EndDateTime}); // number of seconds between the dates
tsecs := remainder(tsecs,86400); // find the left over seconds(86400 is seconds in a day)
numbervar nhours := truncate(tsecs/3600); // divide by the seconds in an hour
tsecs := remainder(tsecs,3600); // find the left over seconds
numbervar nmin := truncate(tsecs/60); // divide by the seconds in a minute
tsecs := remainder(tsecs,60); // find the left over seconds

time(nhours,nmin,tsecs); //Clubing # values with Time() function

If the Date diffrence will exeed 24 hours wee need to calaculate Days also. Like

numbervar tsecs := datediff("s",{StartDateTime},{EndDateTime}); // number of seconds between the dates
numbervar ndays := truncate(tsecs/86400); // divide by the seconds in a day
tsecs := remainder(tsecs,86400); // find the left over seconds(86400 is seconds in a day)
numbervar nhours := truncate(tsecs/3600); // divide by the seconds in an hour
tsecs := remainder(tsecs,3600); // find the left over seconds
numbervar nmin := truncate(tsecs/60); // divide by the seconds in a minute
tsecs := remainder(tsecs,60); // find the left over seconds

if ndays > 0 then
nhours:=nhours+ndays*24;
totext(nhours,0)+":"+ totext(nmin,0)+":"+totext(tsecs,0) //Making A single String

Regards,

Salah.

Former Member
0 Kudos

Thank you. It worked.

Regards

Cauvery