1

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.

  • how did you get 41:35:10 ? and is that your desired final output ? – user0 Feb 7 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 at 0:10
  • how did you get 9 hours per day ? 9:00:00-17:00:00 is 8h – user0 Feb 8 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 at 18:41
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
  • 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 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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