Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# count hours worked in a specific timeframe

Posted on 2011-09-26
Medium Priority
182 Views
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
Question by:douglascarleydotcom
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 26

Expert Comment

ID: 36600201
Hi, Douglas. Could you post a sample, please?

Thanks,
Brian.
0

LVL 50

Expert Comment

ID: 36600245
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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36600542
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

LVL 50

Expert Comment

ID: 36600628
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

Author Closing Comment

ID: 36612002
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.