Calculating TAT of working hours

[rocky09]

How do I calculate TAT of only working hours. 

Lets say, my working hours are 9 AM to 6 PM and Weekends are Saturday and Sunday.

Below is a small example:

Ticket	Type	Received_Time	CompletionDate	Status	Turn Around Time
5545245	Email Inbound	09-02-2018 22:05	12-02-2018 10:00	Completed	2.50
5485452	Email Inbound	08-02-2018 09:05	08-02-2018 10:30	Completed	0.62

The Ticket 5545245 TAT is not correct as the email received after 6PM (Friday), Bascially, it should calculate TAT from Monday. So, the TAT should be 1 Hour.

Can someone help on this?

Thank you in advance,

[WolfBiber]

Hey,

what are you using to calculate TAT? Calc Column or M?

In Both you can use the if (-->DAX) function.

[rocky09]

@WolfBiber

Hi, I am using DAX, difference between two dates. yes, but not able to get the TAT with Working Hours/Weekend logic.

[WolfBiber · Accepted solution]

Hey,

try something like this:

Column = DATEDIFF(
		if(
			WEEKDAY(Table1[Received_Time];2)>=5 && Table1[Received_Time]>timevalue("6:00:00 PM")
			;DATEADD(Table1[Received_Time].[Date]
			;8-WEEKDAY(Table1[Received_Time];2);DAY)
			+TIMEVALUE("09:00 AM")
		;Table1[Received_Time])
		;Table1[CompletionDate]
		;HOUR)

[Technowolf]

Hi

I tried this solutions its not giving the expected results. Can you tell me where I am going wrong.

My Business start date is 8:30 AM and Business End date is 18:00 PM

My TAT should calcuate only Business hours and excluding Weekends.

Business TAT = DATEDIFF(
IF(
'ADX Cases'[Created Business WeekDay]>=5 && 'ADX Cases'[Created Business CET Date]>TIMEVALUE("18:00:00"),
DATEADD('ADX Cases'[Created Business CET Date].[Date],8-'ADX Cases'[Created Business WeekDay],DAY)+TIMEVALUE("08:30:00"),
'ADX Cases'[Created Business CET Date]),
'ADX Cases'[FirstTouched Business CET Date],HOUR)

Created Business CET Date	Created Business CET WeekDay	Case ID	FirstTouched Business CET Date	FirstTouched CET WeekDay	Business TAT	Expected Business TAT
11/29/2019 8:30	5	1	12/2/2019 12:30	1	4	13.5
11/29/2019 11:37	5	2	12/2/2019 15:18	1	7	13.75
11/29/2019 12:45	5	3	12/2/2019 16:20	1	8	13.65
11/29/2019 13:09	5	4	12/3/2019 13:08	2	29	18.97
11/29/2019 14:08	5	5	12/2/2019 9:49	1	1	5.27
12/6/2019 8:30	5	6	12/6/2019 9:35	5	-71	1.05
12/6/2019 8:30	5	7	12/9/2019 8:49	1	0	9.49
12/6/2019 8:30	5	8	12/6/2019 8:40	5	-72	0.20

Regards,

Charles Thangaraj

[rocky09]

Thank you so much @WolfBiber