Forum Discussion
Calculating Business days excluding holidays function with null values
Try this
let
fBusDays = (StartDate as nullable date, EndDate as nullable date, HolidayList as list) as nullable number =>
let
varBusDays =
if StartDate = null or EndDate = null then
null
else
let
// Ensure end date is included by adding 1 to the count
DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, HolidayList),
BusDays = List.Count(RemoveHolidays)
in
if BusDays < 0 then null
else if BusDays = 0 then 1
else BusDays
in
varBusDays
in
fBusDays
You can invoke it like this in a custom column:
fBusDays([Start Date], [End Date], HolidayList)
Make sure HolidayList is a list of dates.
I know I marked this thread a resolved, but I have one more condition that produces errors in this function. And that is when the Start Date is greater than the End Date (unfortunatley, the data I have contains this scenario).
The function produces an Error. I can manually replace the errors with null by going to the “Transform” tab then selecting the drop down for “Replace Values” and choosing “Replace Errors, ” but was wondering if there is a way to edit the function to resolve them.
Thank you again for your help.
I was able to resolve the issue of Start greater than End Date by modifying the function as follows:
let
fBusDays = (StartDate as nullable date, EndDate as nullable date, HolidayList as list) as nullable number =>
let
varBusDays =
if StartDate = null or EndDate = null or StartDate>EndDate then
null
else
let
// Ensure end date is included by adding 1 to the count
DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, HolidayList),
BusDays = List.Count(RemoveHolidays)
in
if BusDays < 0 then null
else if BusDays = 0 then 1
else BusDays
in
varBusDays
in
fBusDays