• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

count hours worked in a specific timeframe

i have a worksheet where i enter the hours worked.  the start time is entered in the odd columns (C, E, G, I, K, M, O) and the end times entered in the even ones (D, F, H, J, L, N, P). our facility is operated in an unusual way.  two organizations manage - in defined times.  in order to bill the other organization, i need to be able to count all the hours each employee has worked during the week.  the other organization operates from 3P to 10P.  so, if an employee works from 2P to 8P, i need a formula that will return the value 5.
0
douglascarleydotcom
Asked:
douglascarleydotcom
  • 3
1 Solution
 
redmondbCommented:
Hi, Douglas. Could you post a sample, please?

Thanks,
Brian.
0
 
barry houdiniCommented:
Will all the times be on the same day? If so then for C2:D2 try

=MAX(0,MIN(D2*24,10)-MAX(C2*24,3))

I'm assuming that cells contain time values. Format result cell as number.Do the same for.other pairs.

regards, barry
0
 
barry houdiniCommented:
Sorry, using 10 and 3 will give you a total of the hours between 3 and 10 AM for 3 and 10 PM it should be 15 and 22 like this

=MAX(0,MIN(D2*24,22)-MAX(C2*24,15))

regards, barry
0
 
barry houdiniCommented:
Here's an example of the above....with several of those formulas joined together...

=MAX(0,MIN(D2*24,22)-MAX(C2*24,15))+MAX(0,MIN(F2*24,22)-MAX(E2*24,15))+MAX(0,MIN(H2*24,22)-MAX(G2*24,15))+MAX(0,MIN(J2*24,22)-MAX(I2*24,15))+MAX(0,MIN(L2*24,22)-MAX(K2*24,15))+MAX(0,MIN(N2*24,22)-MAX(M2*24,15))+MAX(0,MIN(P2*24,22)-MAX(O2*24,15))

....or a slightly shorter "array formula" to get the same result, i.e.

=SUM(IF(D$1:P$1="off",IF(D2:P2*24<15,0,IF(D2:P2*24>22,22,D2:P2*24)))-IF(C$1:O$1="on",IF(C2:O2="",0,IF(C2:O2*24<15,IF(D2:P2*24>15,15,0),IF(C2:O2*24>22,0,C2:O2*24)))))

The latter needs to be confirmed with CTRL+SHIFT+ENTER, see attached

regards, barry
27328144.xlsx
0
 
douglascarleydotcomAuthor Commented:
great solution.  exactly what i asked for.  worked right off the bat.  barryhoudini  is amazing.  he came quickly and did what he had to do.  like a ninja.  or a premature ejaculater.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now