# Calculate work hours between two time/date stamps

• ### 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

• 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.

Regards :-)

• Edited by Thursday, June 11, 2015 2:40 PM
• Marked as answer by 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.

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?

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.

Regards :-)

• Edited by Thursday, June 11, 2015 2:40 PM
• Marked as answer by 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