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]

Hi @Jimmy801 the source data is in another Excel file that is pulled into my current Excel file via power query. I believe this is irrelevant as my exisiting custom column that calculates the time difference in hours (not exlcuding weekends/holidays) works fine i.e.

=try Number.RoundDown(Duration.Days(Duration.From([AKM RFID IN ACCEP DATETIME]-[ACCEPTANCE DATETIME]))) * 24+ Duration.Hours(Duration.From([AKM RFID IN ACCEP DATETIME]-[ACCEPTANCE DATETIME])) otherwise -99

Is there a way to exclude null from your function as the above function does?

[Anonymous]

Hi @Jimmy801 the source data is in another Excel file that is pulled into my current Excel file via power query. I believe this is irrelevant as my exisiting custom column that calculates the time difference in hours (not exlcuding weekends/holidays) works fine i.e.

=try Number.RoundDown(Duration.Days(Duration.From([AKM RFID IN ACCEP DATETIME]-[ACCEPTANCE DATETIME]))) * 24+ Duration.Hours(Duration.From([AKM RFID IN ACCEP DATETIME]-[ACCEPTANCE DATETIME])) otherwise -99

Is there a way to exclude null from your function as the above function does?

[Jimmy801]

Hey

what do you mean by excluding nulls?

Why don't invoke my function with try otherwise... so when wrong parameters are passed to the function you will get -99 in your custom columns?

BR

Jimmy

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

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

[Jimmy801]

Hello

and why did you mark your own post as solution, and not my function??

You should fix that

Jimmy

[Anonymous]

@Jimmy801 amateur mistake, sorry. Fixed now. Thanks again.

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