Finding the difference between 2 date ranges

[pork1977]

Hi all,

I will explain the scenerio first as I don't think I can explain the problem without doing so. I work on a helpdesk that uses call logging software to log calls. These calls can be placed in a HOLD status and when they are, the clock time stops as each one will have specific SLA's to meet.

When the clock is stopped by placing a call on HOLD there becomes an event added to the call called "STOP_CLOCK" and when the call is placed on OPEN again you get another event called START_CLOCK. Both these events are in a table called {Incident_Details.Action ID} and have a date/time stamp added with it.

Now what I have been trying to do is get the date/time of the first event that is STOP_CLOCK and then the last event of START_CLOCK which will hopefully give me the total stopage time within the call. I then need to be able to find the difference between these 2 times and subtract it from the total length of the call (from OPEN to CLOSE) so I can find out the true 'Total WORKING Time'

So far, I've come up with the below formula field which will get me the first clock stop and last clock start times but I cannot find the difference between them. I'm hoping someone can help me as it's taking me all day to get this far! I hope this makes sense.

If {Incident_Details.Action ID} = "START_CLOCK" then
Minimum ({Incident_Details.Date}, {Incident_Details.Action ID}) else

If {Incident_Details.Action ID} = "STOP_CLOCK" then
Maximum({Incident_Details.Date}, {Incident_Details.Action ID})

 

[pork1977]

I've just tried splitting the formula so that now one gets me the start_clock time and the other gets me the stop_clock time then I added a third that would show the difference between them. Didn't think it would work and it didn't as all I got was a load of 0.00 values, not having much luck, I used....

Datediff ("d",{@start_clock time},{@stop_clock time})

 

[RobbieB]

Hey Pork, based on your formula it looks like you are looking for days and are not including hours, minutes and seconds. If this is the case this should work to get your first part:

DateTimeValue (date({@stop_clock time}))
- DateTimeValue (date({@start_clock time}))

Then you can do the same process with the other stop start clocks to get your final time. If @stop_clock time and @start_clock time are already date fields and not date time fields then you can remove the "date" funtion from the formula above.
Good luck

 

[pork1977]

Robbie, thanks for the help, that bit of code was quite useful. I have tried it, I put it into a new formula field called "@diff" but when I preview it I get the value "0.00"

It doesn't seem to be recognising it as a datetime formula? as I noticed when hovering the mosue over the new formula field, it says "@diff (Number)" but if I do the same over the @stop_clock and @start_clock formula fields they say "@stop_clock time (DateTime)" and same for the other. Does this have any relivance?

I've checked the Incident_Details.Date field and is definatly a DateTime field so why the outcome of @diff is in Number format I don't know! You can right click on it then select 'format field' but cannot change to datetime format there.

 

[pork1977]

Silly me, just thought, the format of the @diff formula will need to be in 'Number' format as couldn't have the difference being in a date format! Still working on it...

 

[RobbieB]

You're absolutely right, the result will be a number. Let me know if you need further assistance working this out.

 

[pork1977]

Well, just thought I'd let you know that I've worked around the problem by just deciding to do...

DateDiff ("s", {Incident.Open Date & Time}, {Incident.Close Date & Time})

Unfortunatly it keeps the stoppage times in the results by doing it this way but is ok for the time being. It seems to be when I try doing a DateDiff with formulas then I can't get proper values.

If you think of anything else, I'm all ears! Thanks for helping.

 

[RobbieB]

Hey Pork, believe it or not I just had to do something similar to see how long it took someone to rent ski equipment. Hopefully some of the things I learned can help you.
try this

@TimeFormula
time ({Incident.Close Date & Time}) - time( {Incident.Open Date & Time})/60

This formula will give you your time in minutes.
Then subtract this formula from your total time.

 

[pork1977]

That's great, I wasn't expecting that! I'll try it when I get back in the office tomorrow and will let you know the outcome

Regards,
Paul

 

[RobbieB]

Paul, I hope it works for you. you will probably have to do something similar to your total time. Converting it to minutes then subtracting this formula from your total time formula. I think you get the idea. Good luck.
Rob