PQ - count number of Saturdays and Sundays between two dates

[Patryk_PL_92]

Looking for solution in Power Query to count number of weekend days (Saturdays and Sundays) between two dates.

I currently got below function, but result is incorrect here.

For example:
Initial date is 01.05.2022
Final date is 15.05.2022
Expected result is: 5  

(1,7,8,14,15 of May)

Any idea how to approach that request?

(InitialDate as date, FinalDate as date ) as number => 
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) > 5) ),
WeekendDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WeekendDays

[daXtreme]

Here's code how to calculate the networkdays between two dates:

// 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))

Using this it should be easy to calculate the non-working days. The code above comes from Date.Networkdays function for Power Query and Power BI – (thebiccountant.com).

All you have to do now is to get the total number of days between the two dates and subtract what the function will return.

[Patryk_PL_92]

Hi daXtreme,

that is a very complex code - for sure it will be good inspiration for some of my projects, thank you for that 🙂

[daXtreme]

@Patryk_PL_92 

The code is complex but it does not matter at all. All you have to do is copy it and just use the function. And using it is... no more complex than using any other function in PQ, including the bulitins. But the function above gives you much more than you need. One day you might want, for instance, incorporate a holiday calendar. The function above is ready for that without you having to make a single change.

[rohit_singh · Accepted solution]

Hi @Patryk_PL_92 ,

Please try this custom function:

let parameter = (startdate as text, enddate as text) =>

        let
            Source = {Number.From(Date.From(startdate))..Number.From(Date.From(enddate))},
            #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "List of Dates"}}),
            #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"List of Dates", type date}}),
            #"Calculated Day of Week" = Table.TransformColumns(#"Changed Type1",{{"List of Dates", Date.DayOfWeek, Int64.Type}}),
            #"Filtered Rows" = Table.SelectRows(#"Calculated Day of Week", each [List of Dates] > 4),
            #"Grouped Rows" = Table.Group(#"Filtered Rows", {}, {{"No of weekend days", each Table.RowCount(_), Int64.Type}})
        in
            #"Grouped Rows"

in 
    parameter

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[Patryk_PL_92]

Hi Rohit,

this is perfect, exactly what I was looking for.

The only thing I needed to change is:

let parameter = (startdate as text, enddate as text) =>

change "text" to "date" and then it works 🙂

Thank you

[rohit_singh]

Thanks @Patryk_PL_92 ! Glad to hear it worked for you. Would really appreciate it if you could leave me kudos for the effort! 🙂