2

Given work hours for each weekday (let's say 9:00:00-17:00:00 Monday through Friday), a start timestamp (e.g., 1/15/2019 6:15:15) and end timestamp (e.g., 1/21/2019 14:35:10)...

How can I get the total work hours between start and end, excluding all other hours (i.e., weekends, 17:00:01-8:59:59)?

I'm in need of a basic, reusable formula that does the following for many cases:

  • Start: 1/15/2019 6:15:15
  • End: 1/21/2019 14:35:10
  • Work Hours: 37:35:10

Note again here that I don't want all hours for each day counted, only those within the work hours for each day.

NETWORKDAYS sadly does not give the exact decimal needed.

4
  • how did you get 41:35:10 ? and is that your desired final output ?
    – user0
    Feb 7, 2019 at 23:22
  • The start timestamp ("1/15/2019 6:15:15") occurs before the 9:00:00 start hour for that date (a Tuesday). All of that day, plus Wednesday through Friday of that week, have their work hours counted (for a total of 9 hours per day, 36 hours total). In addition, the end timestamp ("1/21/2019 14:35:10") occurs during work hours, so the "14:35:10 - 9:00:00" remainder (5:35:10) is added to the 36 other hours for 41:35:10 total. That's the output I'm going for. It's a very specific turnaround time calculation.
    – Jon B
    Feb 8, 2019 at 0:10
  • how did you get 9 hours per day ? 9:00:00-17:00:00 is 8h
    – user0
    Feb 8, 2019 at 1:49
  • Through the simple application of arbitrary numbers and rushed math during a caffeine crash, of course! :) Sorry, editing the question now for posterity. Thanks for the answer!
    – Jon B
    Feb 8, 2019 at 18:41

2 Answers 2

0

=NETWORKDAYS(TO_DATE(A2), TO_DATE(B2)) * (E2-D2) -
 IF(INDEX(SPLIT(A2, " "), 1, 2) > D2, D2 - INDEX(SPLIT(A2, " "), 1, 2), ) -
 IF(INDEX(SPLIT(B2, " "), 1, 2) < E2, E2 - INDEX(SPLIT(B2, " "), 1, 2), )

  • column A and column B formatted as Date time
  • column D and column E formatted as Time
  • column C formatted as Duration
2
  • I like what you did. However, to add a huge twist, let's say the "start stamp" can be outside of normal work hours and days. This is the case in reality; I'm measuring work hours that one team has had a ticket, but the filing teams do not have the same hours.
    – Jon B
    Feb 14, 2019 at 21:42
  • I can not reproduce your example. I always got ERROR in C2 cells.
    – dubis
    Mar 26, 2020 at 10:03
0

I ran across this answer when I was trying to ensure we were calculating our response time for business hours. The original formula is great - gives us the number of hours / network day properly but overcorrects for start and end time bc it assumes you can always subtract that when in fact that should only be done when start and end fall on weekdays.

Correcting the original formula for this problem:

The solution is to make it remove hours conditionally on weekdays. I was able to modify the formula above as follows:

= NETWORKDAYS(TO_DATE(A2), TO_DATE(B2)) * (E2-D2) 
  + ( +IF(AND(WEEKDAY(A2, 2) < 6, INDEX(SPLIT(A2, " "), 1, 2) > D2), 
         D2 - INDEX(SPLIT(A2, " "), 1, 2), ) 
      -IF(AND(WEEKDAY(B2, 2) < 6, INDEX(SPLIT(B2, " "), 1, 2) < E2), 
         E2 - INDEX(SPLIT(B2, " "), 1, 2), ))

Addition of the AND(WEEKDAY(A8, 2) < 6, to each of the subtract clauses give us the conditional subtraction we needed in the original. Thanks to the original author for saving me a good chunk of time and making a nice flexible way to specify working hours!

Note also that you can adapt this formula by using NETWORKDAYS.INTL to exclude holidays and alternative weekend days. We didn't need that but others might - exercise for the reader.

Hope this helped someone!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.