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.
Thank you. So here is the edited function:
let
fBusDays = (StartDate as date, EndDate as date, HolidayList as list) as number=>
let
varBusDays = if StartDate = null or EndDate = null then null else
//create series of dates
DateList=List.Dates(StartDate, Number.From(EndDate-StartDate), #duration(1,0,0.0)),
//remove weekends
RemoveWeekends=List.Select(DateList, each Date.DayOfWeek(_, Day.Monday)<5),
// remove holidays
RemoveHolidays=List.RemoveItems(RemoveWeekends, HolidayList),
// count days
BusDays=List.Count(RemoveHolidays),
result =
if varBusDays = null then null
else if varBusDays <0 then null
else if varBusDays = 0 then 1
else varBusDays
in
result
in
fBusDaysBut when I invoke it with a custom column, I get nothing but errors:
???