Time between to dates without weekends and holidays

[Kasper_Clausen]

Hey everbody

I'm new to Power Bi and Power Pivot. I've been trying to search this community and found some diffrent solutuins but can't seem to get them to work.

I want to calculate the number of days between two timestamps but exclude Saturday, Sunday and holidays in the result. So these are incoming requests to a ServiceDesk and then the response.

I got a calendar with holydays marked as 0 and working days marked as 1 so it looks like this:

We measure from the time part to the next time part as well as the date if that makes any sense rather than just from one day to00:00 the next day.

I'm also thinking we might try to calculate it even more accurately because people allocate items to support in the evening but it doesn't get looked at until we are operational the next working day.  I might be really over complicating this though!

Would anyone have any idea if this is possible and if so how to achieve this?

I tried to use this formula: https://community.powerbi.com/t5/Desktop/Calculate-Days-between-two-time-stamps-without-weekends-or/... But it just gave me a result of 1, instead of the difference in days.

I need the measurement from Created day and time, to closed day and time, but without the holydays and time outside the business hour in ServiceDesk. The Business hour in Servicedesk is Monday to Friday from 07:00 to 17:00. (07:00AM to 05:00 PM)

Best regards.

[v-huizhn-msft]

Hi @Kasper_Clausen,

Based on my understanding, you want to create number of days from CreatDate07:00 to ClosedDate17:00? Could you please post your sample data in table rather than picture, it's hard to reproduce your scenario if your data in picture. Thanks for understanding.

Best Regards,
Angelia

[Kasper_Clausen · Accepted solution]

Hi @v-huizhn-msft Angelia

I need the calculation not to include holydas and time outside buisness hours.

Here is the two data sheets as a table:

ClosedDate	ClosedTime	CreatedDate	CreatedTime
30. april 2017	22:30:02	25. april 2017	08:49:03
30. april 2017	22:30:02	27. april 2017	12:29:18
30. april 2017	22:30:02	26. april 2017	10:57:02
30. april 2017	22:30:02	26. april 2017	08:54:25
30. april 2017	22:30:02	27. april 2017	08:42:22
30. april 2017	22:30:02	27. april 2017	07:28:22
30. april 2017	22:30:01	26. april 2017	15:08:43
29. april 2017	22:30:01	26. april 2017	11:22:29
29. april 2017	22:30:02	26. april 2017	09:48:14
29. april 2017	22:30:02	26. april 2017	09:06:10
29. april 2017	22:30:02	26. april 2017	07:50:06
29. april 2017	22:30:02	26. april 2017	07:45:22

DATO	DATEKEY	Month key	Month	Kvartal	Workday	HelligdagsNavn	DayName
1. april 2015	20150401	4	April	2	1		onsdag
2. april 2015	20150402	4	April	2	0	Skærtorsdag	torsdag
3. april 2015	20150403	4	April	2	0	Langfredag	fredag
4. april 2015	20150404	4	April	2	0		lørdag
5. april 2015	20150405	4	April	2	0	Påskedag	søndag
6. april 2015	20150406	4	April	2	0	2. påskedag	mandag
7. april 2015	20150407	4	April	2	1		tirsdag
8. april 2015	20150408	4	April	2	1		onsdag
9. april 2015	20150409	4	April	2	1		torsdag
10. april 2015	20150410	4	April	2	1		fredag
11. april 2015	20150411	4	April	2	0		lørdag
12. april 2015	20150412	4	April	2	0		søndag
13. april 2015	20150413	4	April	2	1		mandag
14. april 2015	20150414	4	April	2	1		tirsdag
15. april 2015	20150415	4	April	2	1		onsdag
16. april 2015	20150416	4	April	2	1		torsdag
17. april 2015	20150417	4	April	2	1		fredag
18. april 2015	20150418	4	April	2	0		lørdag
19. april 2015	20150419	4	April	2	0		søndag
20. april 2015	20150420	4	April	2	1		mandag
21. april 2015	20150421	4	April	2	1		tirsdag
22. april 2015	20150422	4	April	2	1		onsdag
23. april 2015	20150423	4	April	2	1		torsdag
24. april 2015	20150424	4	April	2	1		fredag
25. april 2015	20150425	4	April	2	0		lørdag
26. april 2015	20150426	4	April	2	0		søndag
27. april 2015	20150427	4	April	2	1		mandag
28. april 2015	20150428	4	April	2	1		tirsdag
29. april 2015	20150429	4	April	2	1		onsdag
30. april 2015	20150430	4	April	2	1		torsdag

Best regards

Kasper