Power Query Time difference between two dates/times columns excluding holidays

[Anonymous]

Hi,

I have a custom column in query editor that shows the difference in HOURS of two date/time columns however I did not exclude weekends & holidays.

For this particular dataset, the time calculations has already been created by my colleague (which excludes weekends/holidays) However, his script is in Microsoft Access. I would like to convert his script to power query.  

Is it possible to replicate his logic below in power query? Appreciate any help.

Function CalcHours(STdate As String, STHour As String, EDdate As String, EDHour As String, Etype As String) As Long

Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim y As Long
Dim SStop As Boolean
'' **************** Function to calculate the number of hours between 2 event times ****************

SStop = 0

'' ********* Check to see if the time variables have data ********************
If Not IsNull(STdate) And Not IsNull(EDdate) And EDdate <> "" And STdate <> "" Then
    ' Convert string data to Date and Time data
    StartDate = CDate(STdate & " " & STHour)
    EndDate = CDate(EDdate & " " & EDHour)
    x = 0
    
    Debug.Print StartDate, EndDate  '' Output to immediate window
    Do While StartDate < EndDate And Not SStop
        '' Loop through until the start date and time = the end date and time
        StartDate = StartDate + #1:00:00 AM#  '' Increment the start date variable by one hour
        y = ChkNonWork(StartDate, EndDate, Etype)
        x = x + y
        
        If x > 1000 Then
            SStop = True
            '' Set a trap for an unresolvable loop
        End If
        
    Loop
Else
    ''  If no data set hours = 0
    x = 0
End If
CalcHours = x
End Function

'' *************************************************************************************************************************************

Function ChkNonWork(SDate As Date, Edate As Date, Etype As String) As Long
Dim n As Long

'' ** Check if the date is a weekend or a public holiday, if true remove the hour increment.
n = 1
'Debug.Print Weekday(SDate)
If Weekday(SDate) = 1 Then
    n = 0
    
End If
If is_Pub_Hol(SDate) Then
    n = 0
End If
If Weekday(SDate) = 7 And Etype <> "Delivery" And SDate = Edate Then
    '' Where the event is delivery allow hour increment for Saturday
    n = 0
End If

ChkNonWork = n
        
    
    
End Function

[Jimmy801 · Accepted solution]

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
    HoursSum

have fun

Jimmy

[Anonymous · Accepted solution]

@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'

[Anonymous · 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:)

[Syndicate_Admin]

Hello,

can someone help me change this query to difference in seconds? I could not.

@Jimmy801 

[giovannafnr88]

Hello,

can someone help me change this query to difference in seconds? I could not.

@Jimmy801 

[Jimmy801]

Hello

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

[Anonymous]

@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

[Anonymous]

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 · Accepted solution]

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
    HoursSum

have fun

Jimmy

[Anonymous]

@Jimmy801 after adding the query into my model, I get the following window to enter parameters. What is the purpose of this and what should I do?

[Anonymous]

FYI I integrated your query into my existing query as shown below

YOUR QUERY (StartTime as datetime, EndTime as datetime, ListHoliday as nullable list) as number =>

let

MY EXISTING QUERY

YOUR QUERY

//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 Custom43" = 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
HoursSum

[Jimmy801]

Hello @Anonymous 

and, does it work?

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

Yes it does i.e. after integrating your query into mine, I click "Done" but the "Enter Parameters window" pops up

[Jimmy801]

hey

but you can't integrate it like this

1 - create a new blank query

2 - paste my code

3 - rename query like fxHoursBetweenDates

4 - include this function in your querry - example... add new column (for sure startdatetime and enddatetime have to be in your columns somehow) and then write like this =fxHoursBetweenDates([startdatetim], [endtdatetime])

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

@Jimmy801 ofcourse that makes sense, am learning new things along the way. After following your steps as shown in screenshots below, I get an error after creating custom column

[Jimmy801]

Hello @Anonymous 

you have to paste the code into the advanced editor, not in the formula bar

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

Sorry my bad. Ok so after pasting into Advanced Editor, the resulting Custom Column values shows "Error" and after clicking on Error, the following appears...

[Jimmy801]

Hello @Anonymous ,

try to invoke the function like this: =fxHoursBetweenDates([startdatetim], [endtdatetime], null)

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

@Jimmy801  I tried that but still getting Error. Are you able to replicate from your end?

[Jimmy801]

Hello

Seems to me as if you are feeding the function with a null value. Please share your file

Jimmy

[Anonymous]

Yes some of the cells in the date time columns have 'No Scan' (in the raw data source file) but 'Null' in query editor as per screenshots of sample data below

[Jimmy801]

Hello @Anonymous 

but these databases are not the same, aren't they?

You would need to invoke the funciton only when both columns are filled... meaning that no column contains null or "no scan"

Nevertheless, the initial request was fullfilled, the function works.

So please mark the right answer as solution

Have a nice day

Jimmy