Solved

# Crystal Datediff

Posted on 2014-04-24
253 Views
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
Question by:cdmccoy
• 8
• 5
• 2
• +1

LVL 15

Expert Comment

ID: 40020784
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

Author Comment

ID: 40020838
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

LVL 100

Expert Comment

ID: 40020972
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")
``````

mlmcc
0

Author Comment

ID: 40021031
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

LVL 100

Expert Comment

ID: 40021240
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

Author Comment

ID: 40021290
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

LVL 100

Expert Comment

ID: 40021295
So you want to group an the team field then show the average age in the group footer?

mlmcc
0

Author Comment

ID: 40105549
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

LVL 100

Expert Comment

ID: 40106881
So you only need to see the average for tickets that are less than 7 days old?

mlmcc
0

Author Comment

ID: 40106904
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

Author Comment

ID: 40106909
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

LVL 34

Assisted Solution

James0628 earned 500 total points
ID: 40108172
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

Author Comment

ID: 40108933
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

LVL 100

Expert Comment

ID: 40108961
Do the formulas I added to your other question help with this one?

mlmcc
0

LVL 34

Accepted Solution

James0628 earned 500 total points
ID: 40109443
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

Author Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient â€¦

#### 772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.