Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Crystal Datediff

I forgot how to convert a date to DD:HH:MM:SS using datediff.  This is what I have.

If {Incident.Status} = "Active" then Datediff("d",{Incident.CreatedDateTime},currentDateTime)

It is showing 17:00.  I used the customized under the format field to get the ":"
0
cdmccoy
Asked:
cdmccoy
  • 8
  • 5
  • 2
  • +1
2 Solutions
 
Ess KayEntrapenuerCommented:
time({Incident.CreatedDateTime} - currentDateTime)
then format the resulting formula field to use the "13:23" format option
 
 
...As long as you do not have any result minutes > 24 hours
0
 
cdmccoyAuthor Commented:
I used If {Incident.Status} = "Active" then time({Incident.CreatedDateTime} - currentDateTime)

Then using format "13:23" and the result for the time is showing incorrect.  The incident.createdDateTime is 4/7/2014 2:07PM  to current date.  the result should be 17:XX:XX:XX
0
 
mlmccCommented:
WHat do you want returned from the DateDiff?

DateDIff with d as the units only returns the number of FULL days between the dates so yours is saying there are 17 days.

To get what you want use this formula

Local DateTimeVar testdate := {Incident.CreatedDateTime};
Local NumberVar numDays;
Local NumberVar numHours;
Local NumberVar numMinutes;
Local NumberVar numSeconds;

numSeconds := DateDiff('s',testdate,CurrentDateTime);

numDays := numSeconds \ 86400;  // Number of seconds in a day (24 * 60 * 60)
numSeconds := numSeconds mod 86400;

numHours := numSeconds \ 3600;
numSeconds := numSeconds mod 3600;

numMinutes := numSeconds \ 60;
numSeconds := numSeconds mod 60;

CStr(numDays,0) & ':' & CStr(numHours,"00") & ':' & CStr(numMinutes,"00") & ':' & CStr(numSeconds,"00")

Open in new window


mlmcc
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cdmccoyAuthor Commented:
Yes -- That is exactly what I needed.  If I wanted to group on this value to obtain the average age of the entire group, what variable would I use to sum... would it be testdate
0
 
mlmccCommented:
Are you wanting to group on it or to get the average.  The 2 are very different

Since the value returned by the formula is a string, there is no way to average it.  That would take a different formula

mlmcc
0
 
cdmccoyAuthor Commented:
I would need to group on it.  The individual ticket duration open is good what you sent me, however I need to group on Team so they will just see the overral avg of their incident that are remaining open
0
 
mlmccCommented:
So you want to group an the team field then show the average age in the group footer?

mlmcc
0
 
cdmccoyAuthor Commented:
Yes.  I want to group on the team field and if the tickets are active within a week I want an average time duration of how long the ticket has been open within that week.  The top formula breakes down the days/hours/min/sec which is great but it does not cumulate the total average age.  Thanks for your assistance.
0
 
mlmccCommented:
So you only need to see the average for tickets that are less than 7 days old?

mlmcc
0
 
cdmccoyAuthor Commented:
I need to see all active assignments starting with the Task.CreationDate to CurrentDateTime to see the average time for the team.  The report is grouped on Team.

here is the formula I am using to obtain the total Count of "Active" for the Team.  In most cases the ?Assignment Date is CurrentDateTime.  The task.status "Accepted", "Waiting" indicates the assignment is still open.  Therefore I need to know the Total Average the assignment has been open.

If {Task.CreatedDateTime} <= {?Assignment Date} and
  {Task.Status} in ["accepted", "Waiting"]
0
 
cdmccoyAuthor Commented:
Sorry for any confusion.  At first I was told for the 7 days but they want to see the total average duration of all assignments that are open
0
 
James0628Commented:
You used Incident.Status, Incident.CreatedDateTime and CurrentDateTime in your earlier posts, and Task.Status, Task.CreatedDateTime and {?Assignment Date} in your recent posts.  I'm assuming that the recent posts are correct, but wanted to mention that difference, in case you're actually looking at two different sets of fields (Incident and Task).


 If you're doing a sum on a formula like the following to get the count of active assignments:

If {Task.CreatedDateTime} <= {?Assignment Date} and
 {Task.Status} in ["accepted", "Waiting"] then
  1
else
  0

 Then I think you could use a formula like this:

If {Task.CreatedDateTime} <= {?Assignment Date} and
 {Task.Status} in ["accepted", "Waiting"] then
  DateDiff ("s", {Task.CreatedDateTime}, {?Assignment Date})
else
  0

 That should give you the seconds for each active assignment.  Then you could do a summary on that to get the total seconds for the active assignments, and divide that by the count of active assignments from the first formula.  Something like this:

if Sum ({@first formula above}, {Team group field}) <> 0 then
  Sum ({@second formula above}, {Team group field}) / Sum ({@first formula above}, {Team group field})


 That should give you the average number of seconds for the active assignments for the team, which you could then convert to dd:hh:mm:ss format using a formula like the one that mlmcc posted earlier.

 James
0
 
cdmccoyAuthor Commented:
James -- That is great with the exception I need to get a summary count of the Total Volume which gives me

If {Task.CreatedDateTime} <= {?Assignment Date} and
 {Task.Status} in ["accepted", "Waiting"] then
  1
else
  0

within my running total.  I need the cumulation of the avg age of the Task.Creation date to CurrentDateTime in DD:HH:MM:SS format.  Attached please see the data from the Wireless Team -- I highlighted the COUNT in RED.  The Task.Creation Date to CurrentDateTime should be the Ave of the "Active" incidents.  To make it simple, there are only 3 ACTIVE in this group.  Look at the Task.CreationDate -- The total days are only 20 days old.  I need the Avg from that total which is 10:HH:MM:SS.  I hope that makes sense.
0
 
mlmccCommented:
Do the formulas I added to your other question help with this one?

mlmcc
0
 
James0628Commented:
There was nothing attached to your last post.

 I don't know what you mean by "a summary count of the Total Volume".  If there is one record per task, then a sum of the formula in your last post, for the group, should give you a count of the active tasks for the group.

 What do you mean by "cumulation of the avg age"?  Are you just trying to get the average time for the active tasks for each team; or are you trying to calculate the average for each team, and then get a total of those averages?  "Accumulating the average" kind of suggests the latter.

 James
0
 
cdmccoyAuthor Commented:
The team work was awsome.  I would not have gotten this report completed without both assistance.... Thank you both so much.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now