Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  Game Development

  • Brent Jones

Calculating Business Days and Hours Between Two Dates


Here is a Power Query custom function to calculate the number of business (networking) hours between two dates. All you need to do is just copy and paste this code into a blank query! You shouldn't need to change anything. Very easy!


This excludes weekends and non-business hours using a Power Query custom function. This can then be loaded into the Power BI data model.




// Pass in the datetime columns for first and second date
// Enter a number for the start of business hour. i.e. 9 for 9:00 AM
// Enter a number for the close of business hour. i.e. 17 for 5:00 PM
(
    firstdate as datetime, 
    seconddate as datetime, 
    startofbusiness as number, 
    closeofbusiness as number
   ) as number => 
let 
    // Use a Looping function to Generate hourly intervals
    y = List.Generate(() => 
        firstdate, 
        each _ <= seconddate, 
        each _ + #duration(0,0,30,0)),
    
    // Convert this list to a table
    #"Converted to Table" = 
        Table.FromList(
        y, Splitter.SplitByNothing(), 
        null, null, ExtraValues.Error),
        
    #"Changed Type" = 
        Table.TransformColumnTypes(
        #"Converted to Table",{{"Column1", type datetime}}),
        
    #"Added Custom" = 
        Table.AddColumn(
            #"Changed Type", "WeekDay", each 
            Date.DayOfWeek([Column1]), Int32.Type),

    // Filter out weekend days
    #"Filtered Rows" = Table.SelectRows(
        #"Added Custom", each 
        ([WeekDay] <> 0 and [WeekDay] <> 6)),
        
    #"Added Custom2" = Table.AddColumn(
        #"Filtered Rows", "Time", each 
        DateTime.Time([Column1])),

    // Filter out non-business hours
    #"Filtered Rows1" = Table.SelectRows(
        #"Added Custom2", each 
        [Time] > #time(startofbusiness, 0, 0) and 
        [Time] <= #time(closeofbusiness, 0, 0)),

    // Count rows (half hour intervals), divide by 2 to get hours
    rowcount = Number.Round(Table.RowCount(#"Filtered Rows1")/2,1),
    returncount = if rowcount<0 then 0 else rowcount
in
    returncount

I tried other methods online but they just didn't work. They were too complicated and involved creating separate tables. This method simply creates a custom function that you can use (and re-use) in the Power Query function, Table.AddColumn() .


How to implement:

1. Create a blank query


2. Open the advanced editor


3. Clear out the code



4. Copy and paste this code




5. Rename the query



6. Create a custom column



Keep in mind you'll need to remove or replace Error that result if you have any rows with missing values. This can be easily done using the "Replace Errors" function in Power Query.


One caveat...

One slight caveat is it calculates hours using 30 minute intervals. It's certainly possible to modify this code using single minute increments, however this runs the risk of some serious overhead. The bigger the difference between your two dates, the longer it will take to process. I.e. the current code generates 16 items for every 1 work day (8 time 30 minutes). If you were to use minutes, the code would end up generating 480 items for every 1 work day (60 * 8). This may be fine if your dates are not too far apart. However, if your dates are something like 1 year apart, you'll end up generating 124,800 items (60 minutes * 2080 hours per year). Keep in mind this is applied to each row you have in your data set!


Did this code help you? Please share with someone who you think would also use this!

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Medium_With_Title_Cropp
  • Facebook
  • Twitter
  • Instagram

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Large_With_Title.png