Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in holidays.

Date.Networkdays.pq

// fnNETWORKDAYS
let func =   
(StartDate, EndDate, optional Holidays as list, optional StartOfWeek as number) =>
let
    // optional StartOfWeek, if empty the week will start on Monaday
    startOfWeek = if StartOfWeek = null then 1 else StartOfWeek,

    // cater for empty dates
    StartEmpty = if StartDate = null then #date(1900,1,1) else StartDate,
    EndEmpty = if EndDate = null then #date(1900,1,1) else EndDate,
    
    // Providing for logic where EndDate is after StartDate
    Start = List.Min({StartEmpty, EndEmpty}),
    End = List.Max({StartEmpty, EndEmpty}),

    // Switch sign if EndDate is before StartDate
    Sign = if EndEmpty < StartEmpty then -1 else 1,

    // Get list of dates between Start- and EndDate
    ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)),

    // if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
    // otherwise continue with previous table
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )),

    // Select only the first 5 days of the week 
    // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, startOfWeek) < 5 ),

    // Count the number of days (items in the list)
    CountDays = List.Count(DeleteWeekends) * Sign
in
    CountDays ,
documentation = [
Documentation.Name =  " Date.Networkdays.pq ",
Documentation.Description = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in holidays. ",
Documentation.LongDescription = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in (optional) holidays. ",
Documentation.Category = " Date ",
Documentation.Source = " www.TheBIccountant.com https://wp.me/p6lgsG-2fA .   ",
Documentation.Version = " 3.1 Catering for empty dates",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description =  "  ",
Code = "  ",
Result = "  "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Added option to shift the start of the week-day

Fixed error in step "ListOfDates"

Hardened function (automatic conversion to date)

Adjusted so that negative durations where EndDate is before StartDate can be returned.

Hello... First of all thank you, this has been very helpful.
I would like to ask what needs to be done with the code for it to substract the EndDate and the StartDate? I have Dates with time stamp as well and need a way for the code to take in count the hours as well.
Appreciate all the help you are able to provide around this!

Hello @iBlfxAL ,
I would start with taking this function as it is. As it will return more a longer duration than you need you'd have to calculate the hours to subtract:

• Duration.From( DateTime.Time([StartDate]) - #time(0,0,0)) will give the duration until the StartDateTime and
• Duration.From( #time(24,0,0) - DateTime.Time([EndDate])) will give the remaining hours until the end of the day