How to exclude holidays and weekends in the hours calculation

[PBI_newuser]

Hi,

I have difficulty in calculating the hour different for the below 4 scenario.

1. If End date fall on holiday, exclude it and revise the end date to the latest working hour of the day (11:59:59 PM).
2. If Start date fall on holiday or weekend, exclude it and revise the start date to 12:00:00AM of the next working day.
3. If End date fall on weekend, exclude it and revise the end date to the latest working hour of the day (11:59:59 PM).
4. If there is no working day between start and end date, calculate the different hours between the 2 dates.

Below is an example and "Different in hours" is the expected output.

Scenario	PO Number	Start Date	End Date	Revised Start Date	Revised End Date	Holidays	Weekend	Different in hours
Scenario 1	PO-01517206	02-07-20 16:36	06-07-20 14:00		02-07-20 23:59	3rd & 6th Jul	4th & 5th Jul	7.4
Scenario 2	PO-01520265	05-07-20 12:21	06-07-20 13:45	06-07-20 0:00		6th Jul	5th Jul	13.7
Scenario 3	PO-01641084	09-10-20 17:50	10-10-20 16:30		09-10-20 23:59		10th Oct	6.2
Scenario 4	PO-01649063	17-10-20 3:08	18-10-20 16:00				17th & 18th Oct	36.9

[amitchandak · Accepted solution]

@PBI_newuser , refer if this blog on business hour can help you

https://exceleratorbi.com.au/calculating-business-hours-using-dax/