Solved

count hours worked in a specific timeframe

Posted on 2011-09-26
5
176 Views
Last Modified: 2012-05-12
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
Comment
Question by:douglascarleydotcom
  • 3
5 Comments
 
LVL 26

Expert Comment

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

Thanks,
Brian.
0
 
LVL 50

Expert Comment

by:barry houdini
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

by:
barry houdini earned 500 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

by:barry houdini
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

by:douglascarleydotcom
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question