12 Replies Latest reply on May 11, 2011 11:41 AM by xxx202xxx

# Elapsed Time Calculation Needed Between two Date fields.

### Title

Elapsed Time Calculation Needed Between two Date fields.

### Post

I have looked all over and was not able to locate the calculation that I think I need.  Let me say I don't think I am smart enough to apply what I've read to my situation.  With that said here is my problem.

I have just created a Trouble Ticket which automatically populates a field called "opened" with a timestamp.

I have another field called "closed" that auto-populates a timestamp once the ticket is closed.

I want my third field called "elapsed" to calculate and display the amount of time (in days) that the ticket is open.

Once the ticket is closed I want the "elapsed" field to stop calculating.

• ###### 1. Re: Elapsed Time Calculation Needed Between two Date fields.

Something like this should do the trick:

Let ( Stop = If ( IsEmpty ( ClosedField ) ; Get ( CurrentTimeStamp ) ; ClosedField ) ; ( Stop - OpenedField ) / 86400 )

Make this an unstored calculation so that it regularly updates while open.

Note: The difference of two time stamp fields will be in seconds. Dividing by the number of seconds in a 24 hour day converts seconds to days.

• ###### 2. Re: Elapsed Time Calculation Needed Between two Date fields.

I gave it the ole college try and the results yielded a question mark in the elapsed field.  I need to replace that question mark with a number. 1,2,3. . . . 4.

By the way, what does /86400 mean?

• ###### 3. Re: Elapsed Time Calculation Needed Between two Date fields.

First answer this. What is considered a day? 24 hours? any part of the date?

So if one opens up a ticket at 11:50 PM on Monday and then closes the ticket on 12:05AM on Wed, does that count as 3 days? 2 days? 1 day?

• ###### 4. Re: Elapsed Time Calculation Needed Between two Date fields.

What was your return type? (should be number)

Did you subsitute your field names in place of ClosedField and OpenedField?

• ###### 5. Re: Elapsed Time Calculation Needed Between two Date fields.

24 hours would be considered the day.  Yes I swapped out the example names for my real field names.

• ###### 6. Re: Elapsed Time Calculation Needed Between two Date fields.

that would be the beginning of the 3rd day

• ###### 7. Re: Elapsed Time Calculation Needed Between two Date fields.

By the way, what does /86400 mean?

There are 86400 seconds in a 24 hour period. Since the difference of two time stamp fields will return the difference in seconds, dividing by this value converts to 24hour "days".

You can further refine the results with the Ceiling function if you want a fractional day to be counted as a day or you can round the result to the nearest day using the round function or by formatting your field on the layout.

• ###### 8. Re: Elapsed Time Calculation Needed Between two Date fields.

Here's a demo showing two calculations. One uses Ceiling to count a fractional day as a day.

http://www.4shared.com/file/238460468/c2af1cc/TimeStampDiff.html

• ###### 9. Re: Elapsed Time Calculation Needed Between two Date fields.

Thanks a million.  That works just fine.

• ###### 10. Re: Elapsed Time Calculation Needed Between two Date fields.

Ok, so the above solution work great at the time.  However, the higher-ups wants more dynamic reporting.  I decided to break up the OpenField and ClosedField by creating two separate fields for each.  OpenField is now -> DateCreated and TimeCreated.  ClosedField is now -> DateClosed and TimeClosed with the data types matching, "Date" and "Time".  The above script solution had "Get (CurrentTimeStamp) as part of the script.  I know this can no longer be the case but my question is this;

For elapsed time would it best be calculated by the DateField in this case or the TimeField?

• ###### 11. Re: Elapsed Time Calculation Needed Between two Date fields.

"I decided to break up the OpenField and ClosedField by creating two  separate fields for each.  OpenField is now -> DateCreated and  TimeCreated.  ClosedField is now -> DateClosed and TimeClosed with  the data types matching, "Date" and "Time". "

This is not a good idea if you ever want to compare the entries and they pass over the 24-hour point.  Why do you feel you need to change to individual dates and times?

"For elapsed time would it best be calculated by the DateField in this case or the TimeField?"

If you want increment results smaller than 1 day then you need both in the calculation.  And if you don't then why do you have the time fields to begin with?  :^)

• ###### 12. Re: Elapsed Time Calculation Needed Between two Date fields.

I figured it out.  I changed the Get (CurrentTimeStamp) part of the calculation to Get (CurrentDate) since I am only concern for the days the ticket is opened.  As far as the time field goes, from a Crystal Reports point of view it is easier to manipulate the data than if the two were combined.  I guess it's a preference thing.