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])

[v-shex-msft]

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

[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!