douglascarleydotcom
asked on
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.
Will all the times be on the same day? If so then for C2:D2 try
=MAX(0,MIN(D2*24,10)-MAX(C 2*24,3))
I'm assuming that cells contain time values. Format result cell as number.Do the same for.other pairs.
regards, barry
=MAX(0,MIN(D2*24,10)-MAX(C
I'm assuming that cells contain time values. Format result cell as number.Do the same for.other pairs.
regards, barry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here's an example of the above....with several of those formulas joined together...
=MAX(0,MIN(D2*24,22)-MAX(C 2*24,15))+ MAX(0,MIN( F2*24,22)- MAX(E2*24, 15))+MAX(0 ,MIN(H2*24 ,22)-MAX(G 2*24,15))+ MAX(0,MIN( J2*24,22)- MAX(I2*24, 15))+MAX(0 ,MIN(L2*24 ,22)-MAX(K 2*24,15))+ MAX(0,MIN( N2*24,22)- MAX(M2*24, 15))+MAX(0 ,MIN(P2*24 ,22)-MAX(O 2*24,15))
....or a slightly shorter "array formula" to get the same result, i.e.
=SUM(IF(D$1:P$1="off",IF(D 2:P2*24<15 ,0,IF(D2:P 2*24>22,22 ,D2:P2*24) ))-IF(C$1: O$1="on",I F(C2:O2="" ,0,IF(C2:O 2*24<15,IF (D2:P2*24> 15,15,0),I F(C2:O2*24 >22,0,C2:O 2*24)))))
The latter needs to be confirmed with CTRL+SHIFT+ENTER, see attached
regards, barry
27328144.xlsx
=MAX(0,MIN(D2*24,22)-MAX(C
....or a slightly shorter "array formula" to get the same result, i.e.
=SUM(IF(D$1:P$1="off",IF(D
The latter needs to be confirmed with CTRL+SHIFT+ENTER, see attached
regards, barry
27328144.xlsx
ASKER
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.
Thanks,
Brian.