SOLVED

Excel 2016 query - network + working days between two dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1598279%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20query%20-%20network%20%2B%20working%20days%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1598279%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F733355%22%20target%3D%22_blank%22%3E%40Lisa_Ford%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20recreate%20the%20scenario%20with%20few%20sample%20data%20and%20attached%20in%20the%20Excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esample%20data%20will%20help%20us%20to%20give%20you%20correct%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1600156%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20query%20-%20network%20%2B%20working%20days%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20responding.%20I've%20made%20a%20file%20from%20dummy%20data%20(attached).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20one%20crucial%20step%20that%20is%20inserted%20right%20at%20the%20start%20of%20my%20process.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20I%20want%20to%20do%20this%20as%20a%20query%20is%20that%20the%20raw%20data%20table%20will%20be%20downloaded%20from%20online%20data%20and%20added%20to%20my%20spreadsheet%20weekly%2C%20and%20then%20I%20want%20to%20run%20the%20queries%20to%20update%20my%20pivots%20and%20graphs%20to%20reflect%20the%20new%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3ELisa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1597897%22%20slang%3D%22en-US%22%3EExcel%202016%20query%20-%20network%20%2B%20working%20days%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1597897%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20to%20Excel%202016%20and%20to%20queries%20and%20am%20trying%20to%20create%20a%20new%20custom%20column%20to%20get%20a%20result%20for%20the%20working%20days%20between%20two%20date%20columns%20(dd%2Fmm%2Fyyyy).%20My%20workbook%20also%20has%20a%20table%20for%20public%20holidays%20-%20I'd%20like%20to%20exclude%20these%20as%20well%20as%20weekend%20days.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20I%20can't%20share%20the%20data%20-%20is%20anyone%20able%20to%20help%20me%20with%20the%20correct%20formula%20or%20point%20me%20to%20it%3F%20All%20my%20searches%20seem%20to%20return%20Power%20BI%20solutions%2C%20not%20Excel%20ones.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%2C%3C%2FP%3E%3CP%3ELisa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1597897%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1600813%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20query%20-%20network%20%2B%20working%20days%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1600813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F733355%22%20target%3D%22_blank%22%3E%40Lisa_Ford%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20may%20create%20another%20column%20with%20list%20of%20all%20dates%20in%20the%20range%20for%20each%20record%2C%20remove%20from%20it%20holidays%2C%20transform%20each%20Sat%20and%20Sun%20to%200%20and%20other%20days%20to%201%2C%20sum%20the%20list.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22PPextract_Data%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(Source%2C%7B%22Time%20taken%22%7D)%2C%0A%20%20%20%20%23%22Replaced%20Value%22%20%3D%20Table.ReplaceValue(%23%22Removed%20Columns%22%2Cnull%2CDateTime.LocalNow()%2CReplacer.ReplaceValue%2C%7B%22date_completed%22%7D)%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(%23%22Replaced%20Value%22%2C%7B%7B%22date_received%22%2C%20type%20date%7D%2C%20%7B%22date_completed%22%2C%20type%20date%7D%7D)%2C%0A%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%23%22Changed%20Type%22%2C%0A%20%20%20%20%20%20%20%20%22WorkDays%22%2C%0A%20%20%20%20%20%20%20%20each%20List.Sum(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.Transform(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.RemoveMatchingItems(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.Dates(%5Bdate_received%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Number.From(%5Bdate_completed%5D-%5Bdate_received%5D)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23duration(1%2C0%2C0%2C0)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Holidays%5BHOLIDAYDATE%5D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20(Date.DayOfWeek(_)%20%3D%20Day.Saturday)%20or%20(Date.DayOfWeek(_)%20%3D%20Day.Sunday)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%200%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Replaced%20Errors%22%20%3D%20Table.ReplaceErrorValues(%23%22Added%20Custom%22%2C%20%7B%7B%22WorkDays%22%2C%200%7D%7D)%0Ain%0A%20%20%20%20%23%22Replaced%20Errors%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1603048%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20query%20-%20network%20%2B%20working%20days%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20I%20haven't%20got%20it%20working%20in%20my%20main%20sheet%20yet%20(other%20things%20had%20to%20be%20done%20today).%20I'll%20let%20you%20know%20when%20it%20is%20running.%20I%20am%20very%20appreciative%20of%20you%20sending%20me%20this%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I'm new to Excel 2016 and to queries and am trying to create a new custom column to get a result for the working days between two date columns (dd/mm/yyyy). My workbook also has a table for public holidays - I'd like to exclude these as well as weekend days.

 

Unfortunately I can't share the data - is anyone able to help me with the correct formula or point me to it? All my searches seem to return Power BI solutions, not Excel ones.

 

Thank you in advance,

Lisa

4 Replies

Hi @Lisa_Ford 

 

Please recreate the scenario with few sample data and attached in the Excel

 

sample data will help us to give you correct solution.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

 

@Faraz Shaikh 

 

Thank you for responding. I've made a file from dummy data (attached).

 

This is one crucial step that is inserted right at the start of my process. 

 

The reason I want to do this as a query is that the raw data table will be downloaded from online data and added to my spreadsheet weekly, and then I want to run the queries to update my pivots and graphs to reflect the new data.

 

Thank you for your help!

 

Kind regards,

Lisa

Best Response confirmed by Lisa_Ford (New Contributor)
Solution

@Lisa_Ford 

As variant you may create another column with list of all dates in the range for each record, remove from it holidays, transform each Sat and Sun to 0 and other days to 1, sum the list.

let
    Source = Excel.CurrentWorkbook(){[Name="PPextract_Data"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Time taken"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,DateTime.LocalNow(),Replacer.ReplaceValue,{"date_completed"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"date_received", type date}, {"date_completed", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type",
        "WorkDays",
        each List.Sum(
                List.Transform(
                    List.RemoveMatchingItems(
                        List.Dates([date_received],
                            Number.From([date_completed]-[date_received]),
                            #duration(1,0,0,0)
                        ),
                        Holidays[HOLIDAYDATE]
                    ),
                    each
                        if (Date.DayOfWeek(_) = Day.Saturday) or (Date.DayOfWeek(_) = Day.Sunday)
                        then 0
                        else 1
                )
            )
    ),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"WorkDays", 0}})
in
    #"Replaced Errors"

@Sergei Baklan 

 

Thank you! I haven't got it working in my main sheet yet (other things had to be done today). I'll let you know when it is running. I am very appreciative of you sending me this solution.