Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need some help with the following if possible please?
I need to find the Time to Close time.
I have a Call Open timestamp and a Call Close timstamp and i need to fine the difference beween them to give me the time taken to resolve the call.
But it gets a bit tricky as the working day is between 9-5, so if a call is opened on Tuesday at 10am and closed on Wednesday at 11am then the time would be:
Tues 10am to Tues 5pm = 7hrs
Wed 9am to 11am = 2hrs
Total time taken = 9hrs
I have attached a sample.
Thanks
Does anyone have any ideas?
Try this expression:
(Date#([Closed WorkingDateTime],'DD/MM/YYYY hh:mm')
- Date#([Create WorkingDateTime],'DD/MM/YYYY hh:mm'))*24
- 16 * (floor(Timestamp#([Closed WorkingDateTime],'DD/MM/YYYY hh:mm'))
- floor(Timestamp#([Create WorkingDateTime],'DD/MM/YYYY hh:mm')))
Hi, thanks for this.
When i try it it brings back incorrect results.
I have attached my sample
That's because you're formating it for display as time (the fraction of a day) which it isn't. The difference between created and closed for ID 10001 is fifteen minutes, that's 1/4th of an hour or 0.25 hour. That is the value that is calculated. However, by formatting it as time you get it as 0.25 days which displays as 6 hours and 0 minutes: 6:00
of course, sorry, im having a blonde moment.
How would i display it as days/hours/minutes?
If you give the first expression the name Duration, you can create another one like this:
floor([Duration]/8) & '/' & Mod(floor(Duration),8) & '/' & floor(Frac(Duration)*60)
Hi, thanks that sort of works, the only thing that is wrong now is that if a call is opened on the 1st of the month and closed on the 26th month, the days taken is being reported as 25. but its actually only about 17 as weekends should not be included.
Do you know how i can resolve this? in the script i have a 'Schedule' containing times for weekdays and weekends. Would i also need days?
Thanks
See attached
Can anyone help with this please?
I think you should split the Datetimes in a date and a time field. Then you can simply use the networkdays function to calculate the number of work days between the creation and closing dates: networkdays(CreateDate, CloseDate). If you have dates for holidays available you can add those as additional arguments to the networkdays function.
And you can substract the times to get the hours and minutes with something like if(CloseTime<CreateTime, CloseTime - (CreateTime - 8), CloseTime - CreateTime)