Forum Discussion
Power Query Time difference between two dates/times columns excluding holidays
Hello
I've now adapted the function. has a 3rd parameter that requires a list of days that are representing the holidays
(StartTime as datetime, EndTime as datetime, ListHoliday as nullable list) as number => let //StartTime = #datetime(2019,12,3,8,00,00), //EndTime = #datetime(2019, 12, 8, 8, 0, 0), //ListHoliday = {"03.12.19", "04.12.19"}, ListHolidayCheck = if ListHoliday = null then {} else ListHoliday, ListHolidayInternal = List.Transform(ListHolidayCheck, each Date.From(_)), DurationInHours = (Number.From(EndTime)-Number.From(StartTime))*24, DateTimesFromStartEnd = List.DateTimes(StartTime, DurationInHours, #duration(0,1,0,0)), FilterHolidays = List.Transform(DateTimesFromStartEnd, each if List.Contains(ListHolidayInternal, Date.From(_))= true then null else _), TableWithDateTime = #table({"DateTime"}, List.Zip({FilterHolidays})), #"Added Custom" = Table.AddColumn(TableWithDateTime, "Hours", each 1), #"Inserted Day of Week" = Table.AddColumn(#"Added Custom", "Day of Week", each Date.DayOfWeek([DateTime], Day.Monday), Int64.Type), #"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] < 5)), HoursSum = List.Sum(#"Filtered Rows"[Hours]) in HoursSumhave fun
Jimmy
Jimmy801 thank you for this, much appreciated. Will have a go at creating this into my query model and let you know if I have any issues. If no issues, I will mark this post as 'Accepted Solution'
Jimmy801 yes that worked with the following edit
=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99
Thank you for your help and patience, much appreciated:)
@Jimmy801 yes that worked with the following edit
=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99
Thank you for your help and patience, much appreciated:)
Community ChampionHello
try something like this. This excludes hours on sunday
(StartTime as datetime, EndTime as datetime) as number =>
let
//StartTime = #datetime(2019,12,3,8,00,00),
//EndTime = #datetime(2019, 12, 8, 8, 0, 0),
DurationInHours = (Number.From(EndTime)-Number.From(StartTime))*24,
TableWithDateTime = #table({"DateTime"}, List.Zip({List.DateTimes(StartTime, DurationInHours, #duration(0,1,0,0))})),
#"Added Custom" = Table.AddColumn(TableWithDateTime, "Hours", each 1),
#"Inserted Day of Week" = Table.AddColumn(#"Added Custom", "Day of Week", each Date.DayOfWeek([DateTime], Day.Monday), Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] <> 6)),
HoursSum = List.Sum(#"Filtered Rows"[Hours])
in
HoursSum
have fun
Jimmy
Jimmy801 thank you for the prompt response. Before I create this in power query, just have couple of questions:
1. how do I exclude Saturdays?
2. how do I exclude my country's national public holidays? do I need to create a table?
- Jimmy801
Hello
to exclude also saturadays change the row like this
#"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] <5)),Excatly, you would have to feed the function a list with dates that have to be filtered as well
Have fun
Jimmy
Sorry for the stupid question but when you say feed the function with list of dates, do you mean create a custom column with holiday dates? If yes, then how do I include this in the query you provided? Sorry am still a beginner learning power query...
- Jimmy801
Hello
I've now adapted the function. has a 3rd parameter that requires a list of days that are representing the holidays
(StartTime as datetime, EndTime as datetime, ListHoliday as nullable list) as number => let //StartTime = #datetime(2019,12,3,8,00,00), //EndTime = #datetime(2019, 12, 8, 8, 0, 0), //ListHoliday = {"03.12.19", "04.12.19"}, ListHolidayCheck = if ListHoliday = null then {} else ListHoliday, ListHolidayInternal = List.Transform(ListHolidayCheck, each Date.From(_)), DurationInHours = (Number.From(EndTime)-Number.From(StartTime))*24, DateTimesFromStartEnd = List.DateTimes(StartTime, DurationInHours, #duration(0,1,0,0)), FilterHolidays = List.Transform(DateTimesFromStartEnd, each if List.Contains(ListHolidayInternal, Date.From(_))= true then null else _), TableWithDateTime = #table({"DateTime"}, List.Zip({FilterHolidays})), #"Added Custom" = Table.AddColumn(TableWithDateTime, "Hours", each 1), #"Inserted Day of Week" = Table.AddColumn(#"Added Custom", "Day of Week", each Date.DayOfWeek([DateTime], Day.Monday), Int64.Type), #"Filtered Rows" = Table.SelectRows(#"Inserted Day of Week", each ([Day of Week] < 5)), HoursSum = List.Sum(#"Filtered Rows"[Hours]) in HoursSumhave fun
Jimmy