Solved

count hours worked in a specific timeframe

Posted on 2011-09-26
5
173 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

810 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