none
Calculate work hours between two time/date stamps RRS feed

  • Question

  • Hello,

    I wish to calculate total work hours between two timedate stamps. I hope someone can advise on how to achieve this using Power Query.

    I currently use WORKDAY query in Excel to retrieve work hours using this query:

    =(NETWORKDAYS(I2,K2)-1)*($Q$3-$Q$2)+IF(NETWORKDAYS(K2,K2),MEDIAN(MOD(K2,1),$Q$3,$Q$2),$Q$3)-MEDIAN(NETWORKDAYS(I2,I2)*MOD(I2,1),$Q$3,$Q$2)

    I2=start timestamp
    K2=finish timestamp

    Q2=workday start
    Q3=workday finish

    Thank you.

    Wednesday, April 29, 2015 8:07 AM

Answers

  • Maybe try this one.... I suppose, will meet your needs :-)

    Function fxSOWH (Sum of working hours)

    (WHStart, WHEnd, Start, End, ListOfHolidays) =>
    
    let
    
        DStart             = Number.From(DateTime.Date(Start)), // start day
        TStart             = Number.From(DateTime.Time(Start)), // start time
        DEnd               = Number.From(DateTime.Date(End)),  // end day
        TEnd               = Number.From(DateTime.Time(End)),  // end time
        // List of days without saturdays and sundays
        ListOfNumbers      = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),
        // List of dates without holidays, saturdays and sundays 
        ListOfWorkingDays  = List.Difference(ListOfNumbers,ListOfHolidays),
        SumOfWorkingHours  = if DStart = DEnd then //checking if the start day is the same as the final day
                                if DStart = List.First(ListOfWorkingDays) then // checking if the start day is not a holiday (start day = end day)
                                   List.Median({WHStart, WHEnd, TEnd}) - List.Median({WHStart, WHEnd, TStart}) // working hours if the start day = end day
                                else
                                   0
                             else
                                (
                                 if DStart = List.First(ListOfWorkingDays) then // checking if start day is working day (start day <> end day)
                                    WHEnd - List.Median({WHStart,WHEnd,TStart}) // working hours (for start day)
                                 else
                                    0
                                )
                                +
                                (
                                 if DEnd = List.Last(ListOfWorkingDays) then // checking if end day is working day (start day <> end day)
                                    List.Median({WHStart,WHEnd,TEnd})- WHStart // working hours (for end day)
                                 else
                                    0
                                )
                                +
                          // sum of full-time working hours (start day, end day, holidays, saturdays and sundays excluded)
                                List.Count(List.Difference(ListOfWorkingDays,{DStart,DEnd}))*(WHEnd - WHStart)
    in
        SumOfWorkingHours

    and main query

    let
        TblHolidays   = Excel.CurrentWorkbook(){[Name="tblHolidays"]}[Content],
        LHolidays     = List.Buffer(Table.TransformColumnTypes(TblHolidays,{{"holidays", Int64.Type}})[holidays]),
        TblParameters = Excel.CurrentWorkbook(){[Name="tblParam"]}[Content],
        WHstart       = TblParameters{0}[working hours], //working hours from:
        WHend         = TblParameters{1}[working hours], //working hours to:
        Source        = Excel.CurrentWorkbook(){[Name="tblTimeStamps"]}[Content],
        DTType        = Table.TransformColumnTypes(Source,{{"start", type datetime}, {"end", type datetime}}),
      // Add column with function fxSOWH (SOWH -> Sum Of Working Hours)
        RunFx         = Table.AddColumn(DTType, "Sum of working hours", each fxSOWH(WHstart, WHend, [start], [end], LHolidays))
    in
        RunFx

    Below, link to file on my google drive.

    https://drive.google.com/file/d/0B6UlMk8OzUrxTXJ4UnZnd3UxX00/view?usp=sharing

    Regards :-)


    • Edited by Bill Szysz Thursday, June 11, 2015 2:40 PM
    • Marked as answer by numerouno10 Monday, July 27, 2015 10:19 AM
    Thursday, June 11, 2015 2:38 PM
  • Here's an implementation of Excel's NETWORKDAYS function which doesn't take holidays into account:

    Excel.NetWorkDays = (start, end) =>
        let
            days = Duration.Days(Date.From(end) - Date.From(start)) + 1,
            weeks = if days < 0 then error "invalid range" else Number.RoundDown(days / 7),
            leftover = List.Numbers(Date.DayOfWeek(start), days - (weeks * 7)),
            removed = List.RemoveItems(leftover, {0, 6, 7, 13}),
            extra = List.Count(removed),
            count = weeks * 5 + extra
        in
            count

    As far as I can tell, NETWORKDAYS does not take the time into account.

    Friday, May 1, 2015 12:26 PM

All replies

  • If you have a query that has two columns startTime and fisnishTime you could calculate the difference by

    -Click on Add Column in the query editor

    -Click Add Custom Column button

    -A new window will open, choose the column name then click on the finishTime column to include it in the query

    - enter operation '-' then click the startTime column to include it

    - you should have a query that looks like: [fisnishTime ]-[startTime ]

    -Click Ok

    The new  column with the difference between the two times will be added to your table.


    Thanks, Hadeel

    Wednesday, April 29, 2015 8:12 PM
  • Thanks the complexity comes as I only require total work hours i.e. times between 09:00 - 18:00
    Thursday, April 30, 2015 3:53 AM
  • Did adding custom column solve it? what are the types of start and finish time?


    Thanks, Hadeel

    Thursday, April 30, 2015 4:46 PM
  • Hello,

    The start and finish time can be on different dates and can be outside 09:00-18:00 working hours. Format is "16/04/2015 14:03".

     

    Friday, May 1, 2015 7:54 AM
  • Here's an implementation of Excel's NETWORKDAYS function which doesn't take holidays into account:

    Excel.NetWorkDays = (start, end) =>
        let
            days = Duration.Days(Date.From(end) - Date.From(start)) + 1,
            weeks = if days < 0 then error "invalid range" else Number.RoundDown(days / 7),
            leftover = List.Numbers(Date.DayOfWeek(start), days - (weeks * 7)),
            removed = List.RemoveItems(leftover, {0, 6, 7, 13}),
            extra = List.Count(removed),
            count = weeks * 5 + extra
        in
            count

    As far as I can tell, NETWORKDAYS does not take the time into account.

    Friday, May 1, 2015 12:26 PM
  • Maybe try this one.... I suppose, will meet your needs :-)

    Function fxSOWH (Sum of working hours)

    (WHStart, WHEnd, Start, End, ListOfHolidays) =>
    
    let
    
        DStart             = Number.From(DateTime.Date(Start)), // start day
        TStart             = Number.From(DateTime.Time(Start)), // start time
        DEnd               = Number.From(DateTime.Date(End)),  // end day
        TEnd               = Number.From(DateTime.Time(End)),  // end time
        // List of days without saturdays and sundays
        ListOfNumbers      = List.Select({DStart..DEnd}, each Number.Mod(_,7)>1),
        // List of dates without holidays, saturdays and sundays 
        ListOfWorkingDays  = List.Difference(ListOfNumbers,ListOfHolidays),
        SumOfWorkingHours  = if DStart = DEnd then //checking if the start day is the same as the final day
                                if DStart = List.First(ListOfWorkingDays) then // checking if the start day is not a holiday (start day = end day)
                                   List.Median({WHStart, WHEnd, TEnd}) - List.Median({WHStart, WHEnd, TStart}) // working hours if the start day = end day
                                else
                                   0
                             else
                                (
                                 if DStart = List.First(ListOfWorkingDays) then // checking if start day is working day (start day <> end day)
                                    WHEnd - List.Median({WHStart,WHEnd,TStart}) // working hours (for start day)
                                 else
                                    0
                                )
                                +
                                (
                                 if DEnd = List.Last(ListOfWorkingDays) then // checking if end day is working day (start day <> end day)
                                    List.Median({WHStart,WHEnd,TEnd})- WHStart // working hours (for end day)
                                 else
                                    0
                                )
                                +
                          // sum of full-time working hours (start day, end day, holidays, saturdays and sundays excluded)
                                List.Count(List.Difference(ListOfWorkingDays,{DStart,DEnd}))*(WHEnd - WHStart)
    in
        SumOfWorkingHours

    and main query

    let
        TblHolidays   = Excel.CurrentWorkbook(){[Name="tblHolidays"]}[Content],
        LHolidays     = List.Buffer(Table.TransformColumnTypes(TblHolidays,{{"holidays", Int64.Type}})[holidays]),
        TblParameters = Excel.CurrentWorkbook(){[Name="tblParam"]}[Content],
        WHstart       = TblParameters{0}[working hours], //working hours from:
        WHend         = TblParameters{1}[working hours], //working hours to:
        Source        = Excel.CurrentWorkbook(){[Name="tblTimeStamps"]}[Content],
        DTType        = Table.TransformColumnTypes(Source,{{"start", type datetime}, {"end", type datetime}}),
      // Add column with function fxSOWH (SOWH -> Sum Of Working Hours)
        RunFx         = Table.AddColumn(DTType, "Sum of working hours", each fxSOWH(WHstart, WHend, [start], [end], LHolidays))
    in
        RunFx

    Below, link to file on my google drive.

    https://drive.google.com/file/d/0B6UlMk8OzUrxTXJ4UnZnd3UxX00/view?usp=sharing

    Regards :-)


    • Edited by Bill Szysz Thursday, June 11, 2015 2:40 PM
    • Marked as answer by numerouno10 Monday, July 27, 2015 10:19 AM
    Thursday, June 11, 2015 2:38 PM
  • Wow thank you for such an fantastic answer, perfect.
    Monday, July 27, 2015 10:20 AM
  • Thanks for posting the above regarding Network days I think it is fantastic and almost meets my needs.

    How would you update to compensate and include regions who have different weekends, e.g. Friday and Saturday in Israel? I realize it would be another ListofWorkdays function somewhere just not sure on who to update.

    If I had an additional column with the geo information next to the start and end date, how would the function or the query need to be updated to allow for different working days and still show the time difference for regular weekends and irregular weekends?

    Thanks

    Monday, February 22, 2016 10:56 PM
  • Somebody did it before me :) And I was not suprised that it was you :) 
    Friday, August 18, 2017 5:08 PM
  • HI how do you enter the query above in power BI. I have entered the WHstart and end parameters but not sure how to get the formula above entered

    

    Wednesday, April 11, 2018 12:01 PM
  • Hi Bill,

    I get the following error message when trying to change the holidays table (source excel) into a list:

    "Expression.Error: The name 'TblHolidays' wasn't recognized.  Make sure it's spelled correctly."

    I am using the expression you quoted above i.e. LHolidays     = List.Buffer(Table.TransformColumnTypes(TblHolidays,{{"holidays", Int64.Type}})[holidays]),

    My table is called "Holidays" so not sure what the issue is. Any advice?

    Wednesday, March 20, 2019 12:11 PM