How can I calculate time spent on status between 0900 and 1700?

user_12521517 0
Viewed 59

I have start dates with times and end dates with times tracking when work was entered into a status and when it was completed. I'd like to know how many working hours were spent doing the task.

For example:

Start Date End Date Working Hours (Time Spent between 9 AM - 5 PM)
8/1/2022 10:03 AM 8/5/2022 4:43 PM ?
8/8/2022 9:03 AM 8/10/2022 3:34 PM ?
8/10/2022 11:13 AM 8/15/2022 4:57 PM ?

I'd like to know how many hours were spent working between 9 AM and 5 PM, excluding weekends and public holidays.

How can I do this calculation in Google Sheets?

google-sheets
1 Answers

Try:

=(NETWORKDAYS(A2,B2)-1)*("5:00 PM"-"9:00 AM") +IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"5:00 PM","9:00 AM"),"5:00 PM") -MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"5:00 PM","9:00 AM")

Drag down to column.

Result:

enter image description here

Using NETWORKDAYS() function to only count the working days excluding weekends, then multiply it by the number of hours between 9AM and 5PM.

This will return a number which is in a form of the total number of days. You then format the cell to Duration.

To format the cell:

  • Highlight the cells -> Then on the menu click Format -> Number -> Duration

Final Result:

enter image description here

References:

user_184002720
Related