FInd TAT for working hours excluding weekends and non working hours

[Anonymous]

How do I calculate TAT of only working hours. that should excluded all the weekends and Non Working hours (during weekdays)

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

For example  in the picture StartDateTime is 23rdDec'20 11PM i.e Wed and FinishDateTIme is 26th Dec'20 7AM i.e Sat so technically TAT starts from Thrusday 24th 8AM - 8PM + Friday 25th 8AM - 8PM only i.e 12 +12Hrs = 24 Hrs.
Can someone help with the DAX for the same.

[Anonymous]

Hi @Anonymous 

I have a problem that is start time is 08/17/2021 10:00:00 AM and end time is 08/19/2021 03:00:00 PM , how to calculate the working hours ? Tuesday 08/17/2021 10:00:00 AM -08:00:00 PM + 08/18/2021 08:00:00 AM -08:00:00 PM +08/19/2021 08:00:00 AM -03:00:00 PM , 10+12+7=29 ? Will this still happen in your data ?

If the time is up to the minute, what is the final result you want, can you classify it ?

If possible, please provide us with the handling methods and expected results of different types of time .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

Does this have to be in DAX? I think a column in Power Query might be a better avenue. might. But I am not sure what TAT means, and I'd need data with expected outputs.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous]

Please find the link attached for the input file 

Google Drive Link - Input File 

Output should be like 

StartDateTime is provided as 23rdDec'20 11PM i.e Wed and FinishDateTime is 26th Dec'20 7AM i.e Sat so technically TAT starts from Thrusday 24th 8AM - 8PM + Friday 25th 8AM - 8PM only i.e 12 +12Hrs = 24 Hrs.

Because Start is given at 11PM and our working hrs should be considered only from 8AM it will considered from next day ie 24th 8AM and only till 24th 8PM similarily for 25th. Since 26th is Saturday, we'll close by 25th 8PM and done. So Total Avail Time (hrs) i.e TAT = 12+ 12 hrs = 24 Hrs.

[Anonymous]

@edhans  Hope this helps in understanding.

[Anonymous]

@edhans anyone can guide on this. @amitchandak  or @Greg_Deckler anyone please help.

[Greg_Deckler]

@Anonymous I did this once although I tend to agree with @edhans 

Net Work Duration (Working Hours) - Microsoft Power BI Community