Aug 18 2020 09:45 PM - edited Aug 19 2020 04:48 PM
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
Aug 19 2020 01:36 AM
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
Aug 19 2020 04:47 PM
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
Aug 20 2020 01:30 AM
SolutionAs 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"
Aug 20 2020 11:03 PM
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.
Aug 20 2020 01:30 AM
SolutionAs 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"