Difference of dates in days by excluding weekends (power query)

[Dunner2020]

Hi there,

I want to calculate the difference of two dates in days by excluding weekends in power query. I got a date table which has columns that tells whether the date lies on weekday and weekend.  I used the following formula for calculating the difference in days but dont know how to exclude the weekends. Any help would be really appreciated.

Duration.Days([Date1] - [Date2])

[Anonymous]

Hi @Dunner2020,

Did mahoneypat 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.

If the above doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[mahoneypat · Accepted solution]

Here is one way to do it with a custom column with this formula (Date2 is the earlier date in this case).

= List.Count(List.Select(List.Dates([Date2], Duration.TotalDays([Date1]-[Date2]) + 1, #duration(1,0,0,0)), each List.Contains({1,2,3,4,5}, Date.DayOfWeek(_))))

Pat

[ghaines]

This is incredible, thank you.

I made a revision that hopefully improves performance.  Date.DayOfWeek([Date],1) will offset the value by 1, so you just need to test < 5 instead of compare a list.

Date2 being the earlier date:

= List.Count(List.Select(List.Dates([Date2], Duration.TotalDays([Date1]-[Date2]) + 1, #duration(1,0,0,0)), each Date.DayOfWeek(_,1) < 5))

[IA2000]

Thank you for this - solved a big problem in calculating SLA numbers.

[foodd]

I agree, and used @mahoneypat's reply for the very same purpose in December 2021. 

[Ahmad_Askar]

How beatiful is this code

[MrHippo85]

I believe that the List.Contains({1,2,3,4,5} should actually be List.Contains({0,1,2,3,4}, otherwise this works perfectly! Thank you!