Network Days Custom Function

[AmritaOS]

Hello,

i have created a custom function to calculate network days with removing weekends and holidays. however some rows in my start date and some other rown in my end date have null values resulting in an error in the corresponsing custom column.

Also i want to include some conditional logic in teh custom function - if the network days is a negative value than return a blank.

would anyone have any ideas of how to do this? 

= (StartDate as date, EndDate as date) as number =>
let
ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, PublicHolidays1),
CountDays = List.Count(RemoveHolidays)
in
CountDays

thanks.

Amrita

[AmritaOS]

HI Jimmy,

Thanks very much this works perfectly!!

regards

[Jimmy801]

Hello @AmritaOS 

I changed your code accordingly to give you some ideas how to deal with your requests

= (StartDate as date, EndDate as date) as number =>
let
IntStartDate = if StartDate =null then YourDateIfStartIsNull else StartDate ,
IntEndDate = if EndDate =null then YourDateIfEndIsNull else EndDate ,
ListDates = List.Dates(IntStartDate, Number.From(IntEndDate - IntStartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, PublicHolidays1),
CountDays = List.Count(RemoveHolidays)
in
if IntStartDate > IntEndDate then null else CountDays

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy