cancel
Showing results for
Did you mean:
Frequent Visitor

## Time between to dates without weekends and holidays

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Time between to dates without weekends and holidays

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

2 REPLIES 2
Super Contributor

## Re: Time between to dates without weekends and holidays

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

Frequent Visitor

## Re: Time between to dates without weekends and holidays

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

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 245 members 2,873 guests
Recent signins: