cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
qsmith83 Member
Member

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

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

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Jimmy801 New Contributor
New Contributor

Re: 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
    HoursSum

 

have fun

Jimmy

View solution in original post

qsmith83 Member
Member

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

@Jimmy801 yes that worked with the following edit

 

=try fxHoursBetweenDates([startdatetim], [endtdatetime], null) otherwise -99

 

Thank you for your help and patience, much appreciated:)

View solution in original post

24 REPLIES 24
Jimmy801 New Contributor
New Contributor

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

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

qsmith83 Member
Member

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

@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 New Contributor
New Contributor

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

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

qsmith83 Member
Member

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

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 New Contributor
New Contributor

Re: 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
    HoursSum

 

have fun

Jimmy

View solution in original post

qsmith83 Member
Member

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

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

qsmith83 Member
Member

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

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

 

start-time end-time parameters.png

qsmith83 Member
Member

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

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 New Contributor
New Contributor

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

Hello @qsmith83 

 

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors