# Calculate Working Days using Power Query

• ### Question

• Is there a way to calculate working days ( i.e excluding weekends ) using Power Query between any 2 random dates?

Also if the start date or end date is a weekend I need to subtract this too..

• Edited by Wednesday, January 27, 2016 8:45 AM
Wednesday, January 27, 2016 8:42 AM

• Hi Olivia :-)

Cuple months ago I wrote something like this:

```let
fxWorkingDays = (start as date, end as date, optional HDays as list) as number =>
let
LstOfHolidays = if HDays = null then {} else HDays,
LstOfDays = {Number.From(start)..Number.From(end)},
LstDiff  = List.Difference(LstOfDays, LstOfHolidays),
LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),
LstSel = List.Select(LstMod, each _>1),
Result = List.Count(LstSel)
in
Result,
Holidays_Import =  try Excel.CurrentWorkbook(){[Name="Holidays"]}[Content] otherwise Table.FromColumns({{}}, {"Holidays"}),
Holidays = List.Buffer(Table.TransformColumnTypes(Holidays_Import,{{"Holidays", type number}})[Holidays]),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChType = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
NumberOfWorkingDays = Table.AddColumn(ChType, "Workdays", each fxWorkingDays([StartDate], [EndDate], Holidays))
in
NumberOfWorkingDays```

"Table1" contains raw data ( "StartDate" and "EndDate").
Table "Holidays" if exist, contains dates of holiday.
The syntax of the function "fxWorkingDays" is exactly the same as Excel NetworkDays function.
It does not require  calendar table, and the table of holidays is optional.

Regards :-)

• Marked as answer by Thursday, January 28, 2016 5:33 AM
Wednesday, January 27, 2016 5:34 PM

### All replies

• Hi Olivia :-)

Cuple months ago I wrote something like this:

```let
fxWorkingDays = (start as date, end as date, optional HDays as list) as number =>
let
LstOfHolidays = if HDays = null then {} else HDays,
LstOfDays = {Number.From(start)..Number.From(end)},
LstDiff  = List.Difference(LstOfDays, LstOfHolidays),
LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),
LstSel = List.Select(LstMod, each _>1),
Result = List.Count(LstSel)
in
Result,
Holidays_Import =  try Excel.CurrentWorkbook(){[Name="Holidays"]}[Content] otherwise Table.FromColumns({{}}, {"Holidays"}),
Holidays = List.Buffer(Table.TransformColumnTypes(Holidays_Import,{{"Holidays", type number}})[Holidays]),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChType = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
NumberOfWorkingDays = Table.AddColumn(ChType, "Workdays", each fxWorkingDays([StartDate], [EndDate], Holidays))
in
NumberOfWorkingDays```

"Table1" contains raw data ( "StartDate" and "EndDate").
Table "Holidays" if exist, contains dates of holiday.
The syntax of the function "fxWorkingDays" is exactly the same as Excel NetworkDays function.
It does not require  calendar table, and the table of holidays is optional.

Regards :-)

• Marked as answer by Thursday, January 28, 2016 5:33 AM
Wednesday, January 27, 2016 5:34 PM
• Alternately, you can create a function that does this without needing to perform joins:
```let
WorkDaysBetween = (start as date, end as date, dimdate as table) =>
Table.RowCount(
Table.SelectRows(
dimdate
,each
( ([Date] >= start and [Date] <= end)
and [WorkDayFlag] = true )
)
)
in
WorkDaysBetween```

This should get you there, it might need some minor tweaking.

Wednesday, January 27, 2016 5:37 PM
• Thank you all..

Bill, it worked with some tweaks. Thank you :-)

Thursday, January 28, 2016 5:34 AM
• Please, I can notwork it out:

I have twotables in Power BI Desktop

How do Iapply thissolution?

I need of working days between two dates, but I can not apply the function.

dates

start end
25/01/2016    26/02/2016
26/12/2015 15/01/2016
15/12/2015 25/01/2016

holidays

holidays
01/01/2016
24/12/2015
25/12/2015
Wednesday, February 3, 2016 5:01 PM
• Hello Bill,

Your post helps me a lot, in a fact your logic to find working days is brilliant, that was the clue for me, and honestly it tooks me some time to understand it. But base on it I have simplified only to function which can be used directly in custom column in any query and includes also negative values (delays). So here is:

`let WorkingDays = (start as date, end as date, optional HDays as table) as number =>   let  LstOfHolidays = if HDays = null then {} else Table.Column(HDays,"NameOfYourCalendarColumn"),  NumLstOfHolidays = List.Transform(LstOfHolidays ,each Number.From(_)),  LstOfDays = if start<end then {Number.From(start)..Number.From(end)} else {Number.From(end)..Number.From(start)},  LstDiff  = List.Difference(LstOfDays, NumLstOfHolidays),  LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),  LstSel = List.Select(LstMod, each _>1) ,  Result = if start<end then List.Count(LstSel) else List.Count(LstSel)*(-1)in  Resultin  WorkingDays`

Where:

start – Start date

end – End date

HDays – Is table with holidays, where one column has holidays from one calendar . In my case are different subcontractors calendars, each one in his proper column.

Regards

• Edited by Monday, December 12, 2016 9:13 PM
Monday, September 26, 2016 9:27 AM
• Hello Bill

As KraBo mentiones above, the function sometimes produces negative figures for a sameday result.

I have 14/05/2018 for RECEIVEDDATE and the same 14/05/2018 for Date Completed, and my answer is -1 ??