1 2 Previous Next 16 Replies Latest reply on Apr 4, 2016 1:52 AM by mahi reddy

# Lifetime of a ticket over business hours 6am-6pm

I am trying to calculate the hours of a ticket's lifetime over business hours based on a start date/time and an end date/time. I have looked over a few threads that exclude holidays and weekends from calculations but some of them seem to use a method that would not work where they shifted the start date rather than excluding weekends.

For example if a ticket had a start date/time of 10/23/2015 4:00 PM (Friday at 4) and an end date/time of 10/26/2015 9:00 AM (Monday at 9 am) then it would have a business time of completion to be 5 hours as opposed to 65 hours that results from Datediff("hour",[Submit Date],[Last Resolved Date])

Any help would be appreciated. I have tried a few forum post's approaches to no avail. I have attached the relevant parts of a sample of tickets.

Thank You!

• ###### 1. Re: Lifetime of a ticket over business hours 6am-6pm

Something along these lines should get what's needed:

DATEDIFF('hour', [Submit Date], [Last Resolved Date])

-

(DATEDIFF('day',[Submit Date],[Last Resolved Date]) * 16)

-

( DATEDIFF('week',[Submit Date],[Last Resolved Date],'monday') * 16)

• ###### 2. Re: Lifetime of a ticket over business hours 6am-6pm

I tried that out on the excel i posted as well as the example i wrote about

"if a ticket had a start date/time of 10/23/2015 4:00 PM (Friday at 4) and an end date/time of 10/26/2015 9:00 AM (Monday at 9 am) then it would have a business time of completion to be 5 hours as opposed to 65 hours"

when i plugged that example in i came out with a result of 1. (65)-(48)-(16) = 1

I need more than just a calculation of hours worked per day. The only hours that count would be the ones within the time period of 6am-6pm and exclude any time outside of M-F 6am-6pm that the ticket is "alive".

• ###### 3. Re: Lifetime of a ticket over business hours 6am-6pm

How about below with maybe some additional tweaking?  You will have to figure out the calc to get the Submit Date and Last Resolved Date rounded to 6am and 6pm but I'm sure it's possible.

if DATEDIFF('day', [Submit Date],[Last Resolved Date]) = 0 then DATEDIFF('hour',  [Submit Date], [Last Resolved Date])

else

DATEDIFF('hour', [Submit Date], [Submit Date]@6PM)    //Hours on First Day

+ DATEDIFF('hour', [Last Resolved Date]@6AM, [Last Resolved Date])   //Hours on Final Day

+(DATEDIFF(‘day’, [Submit Date], [Last Resolved Date])-1) * 8       //Hours for days between

-(DATEDIFF('week', [Submit Date],[Last Resolved Date],'monday') * 16)     // Minus Hours for Weekends

end

• ###### 4. Re: Lifetime of a ticket over business hours 6am-6pm

I modified it further:

if DATEDIFF('day', [Submit Date],[Last Resolved Date]) = 0 then DATEDIFF('hour',  [Submit Date], [Last Resolved Date])

else

DATEDIFF('hour', [Submit Date], [Submit Date]@6PM)    //Hours on First Day

+ DATEDIFF('hour', [Last Resolved Date]@6AM, [Last Resolved Date])   //Hours on Final Day

+(DATEDIFF(‘day’, [Submit Date], [Last Resolved Date])-1) * 12      //Hours for days between

-(DATEDIFF('week', [Submit Date],[Last Resolved Date],'monday') * 24)     // Minus Hours for Weekends

end

1 of 1 people found this helpful
• ###### 5. Re: Lifetime of a ticket over business hours 6am-6pm

Hmm, that concept looks really solid, now i just have to figure out how to implement it within the data set. Thanks!

im thinking creating another field that calculates the [submit date]@6pm by using DATETRUNC('day',[submit date]) then setting the time for that variable to 6pm

• ###### 6. Re: Lifetime of a ticket over business hours 6am-6pm

Yes the DATETRUNC should do it along with DATEADD ('hour', 6 or 18, [Submit Date or Last Resolved Date truncated to day]).

1 of 1 people found this helpful
• ###### 7. Re: Lifetime of a ticket over business hours 6am-6pm

im now using:

if

DATEDIFF('day', [Submit Date],[Last Resolved Date]) = 0

then

DATEDIFF('hour',  [Submit Date], [Last Resolved Date])

else

DATEDIFF('hour', [Submit Date], [submit day])

+ DATEDIFF('hour', [last date], [Last Resolved Date])

+(DATEDIFF('day', [Submit Date], [Last Resolved Date])-1) * 12

-(DATEDIFF('week', [Submit Date],[Last Resolved Date],'monday') * 24)

end

where

[submit day] = DATEADD('hour',18,DATETRUNC('day',[Submit Date]))
and
[last date] = DATEADD('hour',18,DATETRUNC('day',[Last Resolved Date]))

but I have noticed that if a ticket is submitted on a sunday and resolved on a monday, it becomes negative. Im trying to change the if else statements to accommodate these possibilities.

• ###### 8. Re: Lifetime of a ticket over business hours 6am-6pm

Maybe creat a modified submit date where if it submitted after 6pm on Friday then equals 6 AM on Monday.  You will need to something similar for Last Resolved Date as well.

• ###### 9. Re: Lifetime of a ticket over business hours 6am-6pm

Here is a calculation that combines  John Sobczak's  with  Calculating the Number of Business Days Between Two Dates | Tableau Software  plus extra validating logic:

```// hours start date
IF     DATEPART('weekday',[Start Date Time]) = 1 THEN 0
ELSEIF DATEPART('weekday',[Start Date Time]) = 7 THEN 0
ELSE   DATEDIFF('hour',[Start Date Time 2],DATEADD('hour',18,DATETRUNC('day',[Start Date Time])))
END +

// hours end date
IF     DATEPART('weekday',[End Date Time]) = 1 THEN 0
ELSEIF DATEPART('weekday',[End Date Time]) = 7 THEN 0
ELSE   DATEDIFF('hour',DATEADD('hour',6,DATETRUNC('day',[End Date Time])),[End Date Time 2])
END +

// the other days
12 * (
DATEDIFF('week',[Start Date Time],[End Date Time])*5
+ MIN(DATEPART('weekday',[End Date Time]),6)
- MIN(DATEPART('weekday',[Start Date Time]),6)
- IF     DATEPART('weekday',[End Date Time]) = 1 THEN 0
ELSEIF DATEPART('weekday',[End Date Time]) = 7 THEN 0
ELSE 1
END
) +

// holiday 28 October 2015
IF #2015-10-28# > [Start Date Time] AND #2015-10-28# < DATETRUNC('day',[End Date Time]) THEN -1
ELSE 0
END
```

Holidays are added since  Shawn Wallwork  mentioned this issue. These are a pain and therefore added a single manual example to illustrate one way to do it. This would be much easier if something like  Custom Calendar Tables  was implemented and therefore hope mentioned idea gets many more up-votes.

If you use this one, I recommend you to test all thinkable scenarios. I so did, but just thought it also needs to be tested for multiple weekends with start and end dates touching weekends. I can't do so now myself, because I need to do something else.

Attached Workbook Version:  9.0

.

1 of 1 people found this helpful
• ###### 10. Re: Lifetime of a ticket over business hours 6am-6pm

But what about the holidays?

• ###### 11. Re: Lifetime of a ticket over business hours 6am-6pm

But what about the holidays?

Good point. I just hard-coded an imagined holiday (28 October 2015) into it and updated  this comment.

That said, it would be so much nicer if we had something like  Custom Calendar Tables

1 of 1 people found this helpful
• ###### 12. Re: Lifetime of a ticket over business hours 6am-6pm

I have noticed that if a ticket is submitted on a sunday and resolved on a monday, it becomes negative.

Oops, my calculation was logically built to always included the time on start and end dates.

This is corrected and hope the calculation returns correct hours now. See details in  this comment.

• ###### 13. Re: Lifetime of a ticket over business hours 6am-6pm

Sory to return to this after so long, but I have run into an issue that maybe you can help me with. I am trying to use the same calculation to filter out holidays, but rather than coding in the actual date, I would like to use a list in excel. Here is the code im using:

// hours start date

IF DATEPART('day',[Start Date])= DATEPART('day',[Holiday]) then 0

ELSE

IF     DATEPART('weekday',[Start Date]) = 1 THEN 0

ELSEIF DATEPART('weekday',[Start Date]) = 7 THEN 0

ELSE   DATEDIFF('hour',[Start Date],DATEADD('hour',18,DATETRUNC('day',[Start Date])))

END

END +

// hours end date

IF DATEPART('day',[End Date])= DATEPART('day',[Holiday]) then 0

ELSE

IF     DATEPART('weekday',[End Date]) = 1 THEN 0

ELSEIF DATEPART('weekday',[End Date]) = 7 THEN 0

ELSE   DATEDIFF('hour',DATEADD('hour',6,DATETRUNC('day',[End Date])),[End Date])

END

END +

// the other days

12 * (

DATEDIFF('week',[Start Date],[End Date])*5

+ MIN(DATEPART('weekday',[End Date]),6)

- MIN(DATEPART('weekday',[Start Date]),6)

- IF     DATEPART('weekday',[End Date]) = 1 THEN 0

ELSEIF DATEPART('weekday',[End Date]) = 7 THEN 0

ELSE 1

END -

//Holidays

IF [Holiday] > (DATEPART('day', [End Date])) AND [Holiday] < (DATEPART('day',[Start Date])) then 1

ELSE 0

END

)

I have included the list intend to use(i have it in an excel file but i cannot figure out how to attach it) but when I try to run it i get errors about aggregation and data sources and other things. If you are testing with the superstore i believe that order date and shipped date can be used in leu of start and end date.

I dont account for the possibility that the holidays fall on the weekends because the list i am using is government holidays and therefore would never fall on a weekend. -in order for results in the superstore, you may have to switch all the 2015 to 2014.

 day name holiday Thursday, January 1 New Year’s Day 1/1/2015 Monday, January 19 Birthday of Martin Luther King, Jr. 1/19/2015 Monday, February 16* Washington’s Birthday 2/16/2015 Monday, May 25 Memorial Day 5/25/2015 Friday, July 3** Independence Day 6/3/2015 Monday, September 7 Labor Day 9/7/2015 Monday, October 12 Columbus Day 10/12/2015 Wednesday, November 11 Veterans Day 11/11/2015 Thursday, November 26 Thanksgiving Day 11/26/2015 Friday, December 25 Christmas Day 12/26/2015

Thank You.

• ###### 14. Re: Lifetime of a ticket over business hours 6am-6pm

Thanks kettan

I am also facing the same issue. The solution which you have mentioned resolved my issue.

I did a slight modification to the calculated field which you have created:

I have removed the 'Datetrunc' function, because while using DATETRUNC('day',[End Date Time]) function also it is still considering hours instead of days.

Modified calculated field:

// holiday 28 October 2015

IF #2015-10-28# > [Start Date Time] AND #2015-10-28# < [End Date Time] THEN -12

ELSE 0

END

Q) I have the holiday list like January 1, January 19...and so on. I have to apply the above calculation for every date in the holiday list? or any alternate solution?

1 2 Previous Next