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.
douglascarleydotcomAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.