Calculate the total overlapping time of multiple tasks, excluding non-working times

I am developing a tracking calculator for an overall process. Within the
overall process, there are 3 steps which are completed. These steps can
be completed independently from one another, but can also (sometimes)
overlap.

I am trying to determine the total time for the overall process (from
beginning to end), without counting the duplicated times where the
steps are running parallel to one another, or times when some steps may
be completed and are waiting for another to start.

I have outlined the basic design of the worksheet below, with times in
mm/dd/yyyy hh:mm format. 

Step 1   Start Time (B5)     Complete (C5)
Step 2   Start Time (B6)     Complete (C6)
Step 3   Start Time (B7)     Complete (B8)

I have already accounted for the total working hours (9am to 5pm) for
each individual step, counting only work hours, excluding holidays and
weekends, using the formula below (example of step 2, which would be in
Cell D6):

=IF(AND(INT(B6)=INT(C6),NOT(ISNA(MATCH(INT(B6),K5:K15,0)))),0,ABS(IF(INT(B6)=INT(C6),ROUND(24*(C6-B6),2),
(24*(M5-L5)*
(MAX(NETWORKDAYS(B6+1,C6-1,K5:K15),0)+
INT(24*(((C6-INT(C6))-
(B6-INT(B6)))+(M5-L5))/(24*(M5-L5))))+
MOD(ROUND(((24*(C6-INT(C6)))-24*L5)+
(24*M5-(24*(B6-INT(B6)))),2),
ROUND((24*(M5-L5)),2))))))

The problem is, I cannot find a way to account for the TOTAL time,
which will calculate the overlapped time, or holding times between
processes. 

An example of my dilema:

If step 1 starts at 5/14/2010 4:00 pm and ends 5/17/2010 12:00 pm - the
step took a total of 5 working hours.

If Step 2 starts at 5/14/2010 3:00 pm and ends 5/17/2010 2:00 pm - the
step takes a total of 7 working hours.

If Step 3 starts at 5/17/2010 4:00 pm and ends 5/18/2010 10:00 am - the
step takes a total of 2 working hours.

Total for all three steps would be 14 working hours. What I need is a
formula that will also look at the times for overlaps, gaps, and give
me a result that would indicate took a total of non-duplicate working
hours, which would be 9.

I would use a MIN/MAX function, factoring in the NETWORKDAYS, but there
are sometimes when there are gaps between the steps. For instance, the
example above shows a hold time between steps 1&2, and step 3 (2 hours
of no work being done.)

This may not be possible in Excel, but I thought I would reach out to
see if anyone can provide some assistance.

Thank you,




-- 
Ryan A
0
Ryan
5/17/2010 6:18:15 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1949 Views

Similar Articles

[PageSpeed] 27

Excel 2007 Tables
Advanced Filter
Filter weekends, holidays, working hours,
and overlapping time periods.
With optional chart and macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/05_18_10.xlsx
Pdf preview (3 pgs)
http://www.mediafire.com/file/vkhmyvbnnjm/05_18_10.pdf

0
Herbert
5/19/2010 5:32:42 PM
Reply:

Similar Artilces:

time quick entry
I would like to do quick entry for times to the hundredth of a second (e.g., m:ss.00). I've used Chip Pearson's method for time quick entry as a starting point, and edited the Cases, but it's not working for me. I've included the code I've tried below. Thanks for any help, ~ Horatio Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count > 1 Then Exit Sub End If If Target.Value = "" Then ...

Run-time error '2147217900 (80040e14)
I have a tree list control I want to populate with Categories based on a Client. The client number is input into a text box & then the update event populates the tree based on the selection. I get a syntax error when it tries to open the recordset based on the SQL. Two tables are invloved (Client & Categories; in a 1-to-many relationship on pID field). Here'e the code: Dim rstCategory As New ADODB.recordset, rstComponent As New ADODB.recordset Dim rstSubComponent As New ADODB.recordset Dim tvwTree As Object Dim nodX As Node Dim I As Integer Dim blnAllRecs As B...

Junk Mail and Detect and Repair wont work in Office 2003
I am trying to update the Outlook Junk Mail filters, KB914454 it wont let me in Office 2003 it continually shows the shield icon saying I have an update to complete when I do it fails, tried to repair, that fails too, should I try install even tho my software or CD is Office 2003 and I have SP2 on the computer already or is there a registry fix, do I really need Junk Mail, how can I stop this update from happening, I use AVG and it works fine ...

Working with Managed Allocation Funds
Are there any recomendations for working with managed allocation funds that don't have ticker symbols, but are made of up funds that do. For example, I have a CHET 529, which has as one of its options an "Agressive Managed Allocation Fund". That fund is actually comprised of a set of TIAA-CREF Funds (Equites, International Equity, Real Estate, etc.) which do have ticker symbols. Is there any way to set up this mapping, or should I just manually configure the asset allocation for the "wrapper" fund? Regards, Brian In microsoft.public.money, BrianLeonard wrote: &...

Calculating dates
A quick question before i'm off to bed :) I got several dates, all in the future. I'll just give an example, cause it'll be hell to explain otherwise :p I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I need to know how many days have past from 26-07-2004 untill now. So, day and month stay the same and year needs to change to the year before the current one. Then count the days untill the date now. I need this to calculate accrued intrest, should someone wonder (or if that makes it easier to understand ;) ) Preferably, a short function (if possible) since it...

storing multiple list selections in access
i have 2 tables, table a and table b. is it possibile for a control bound to table a (a list box, with multiple selections eneabled) to be able to select multiple records from table b, and store them? i.e. table A record 1 has table B records 1,2,3,4,8,12, and so on selected while Table A record 2 only has table B records 3, 8 and 15 selected, and so on.. is there some way to store those selections within table A? for those curious, i'm making a character sheet for an RPG.. table A is the actual sheet, while B is the list of spells.. i'm looking for a way to store which spells each ...

My forms will not work with my email when I click submit.
Sending Form Data via E-mail, it will open up my Email but, the data is NOT contained in the body of the e-mail message. The e-mail address I entered for the ACTION attribute shows up. BUT NOT DATA IS CONTAINED IN THE BODY. ***************************** <FORM METHOD=POST ENCTYPE="text/plain" ACTION="pamjck@juno.com"> Name: <INPUT TYPE="text" NAME="name"> <INPUT TYPE="submit" VALUE="submit button"> ******************************** why is my Email NOT working anymore and adding the data to the body of the e-mail ...

getting sql report 2005 web service to work
The error I am getting in sql reporting service 2005 is 'The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)'. There are lots of answers on the web and I have tried alot of them including obtaining extract space. The error I am getting is: " The report is still being rendered or a report snapshot is not available. (rsReportNotReady)". I am using using windows-authentication to the datasource, and I am wondering if I am not setting up the pass-through properly on t...

countif function with multiple criteria
Is it possible to have multiple criteria for a countif function? Thanks. Not COUNTIF, but SUMPRODCT will =SUMPRODUCT(--(rng1="value1"),--(rng2=num2)) rng1 and rng2 must be the same size, and cannot be complete columns. -- HTH RP (remove nothere from the email address if mailing direct) "Geoff" <geoffreynd@yahoo.com> wrote in message news:1123798529.834293.79080@g14g2000cwa.googlegroups.com... > Is it possible to have multiple criteria for a countif function? > Thanks. > ...

IF Statement won't work
I am trying to include an IF statement on a spreadsheet using Excel 97, but the programme always interprets it as a text statement. I'm using the syntax IF(B4>A3,D1,0) or (test,value if true,value if false) which appears to be correct but the problem won't go away. Any ideas? Thanks in advance! Did you type an = sign in front of the IF? =IF(B4>A3,D1,0) The = tells Excel this is an expression, not a string. Lee Schipper "Caliban" <mrkleen@nospamprimus.com.au> wrote in message news:41b28dbb$1_1@news.iprimus.com.au... > I am trying to include an IF state...

Sending an email to multiple contacts
As suggested by people on one of these newsgroups, I have abandoned Outlook's broken distribution list functionality to use this "Categorize" feature. So I now have my hundreds of individuals all categorized and I want to send an email to everyone in a certain category. I do not see any sign that this functionality is supported, in fact, it seems like Microsoft actually broke the functionality intentionally. I have created my email and have opened my contacts. I sorted based upon category, selected the items in the category and dragged those items to the "To.....

Multiple remit-to addresses per vendor
Is there a good way to handle multiple remit-to addresses per vendor? It seems like the only solution is to create one vendor per remit-to address (resulting in a bunch of vendors) or manually switching the remit-to address prior to printing checks (opening the door to more error). You can have an unlimited number of addresses for your vendors. Each address is titled with an address code which must be unique. On the vendor card you choose ONE address as the default Remit To but you can perform a remit to function to any address. cr >-----Original Message----- >Is there a good...

When contacts are associated to multiple Companies
I was hoping someone can give me some ideas on best practices for this situation: We are recording cases and when possible defining the customer field as a contact. Many times, consultants will call in for a company requesting support. We want to track the case as associated to the Account they are calling from as well as the true person who is calling in. A consultant would work for a company other than the one they are representing. Right now I am having to create that person more than once in order to associate the case to the caller/contact and the account that they are representing w...

Editing tasks.... how??
I have outlook all set up and am excited about getting it rolling but when I went in today to update a task it would allow me to click in the body but not type anything. It did not give any warnings it just did not type. Text box would not work... nothing. I do have it linked to my blackberry so I went to my blackberry and made the change but to be honest that is NOT going to work, if I have to do that EVERY time I want to update a task. I dont have anything assigned to anyone and other than it being linked to my blackberry it is linked to someone else. Any ideas on how to fix this??...

Multiple fields in stLinkCriteria
On form_1, I have a subform. I want to access form_2 using data from form1 and its subform. On the subform 'double_click() I coded: Dim stLinkCriteria As String stLinkCriteria = "([field_a] = """ & Me![field_a] & _ """) AND ([field_b] = """ & [form1].[field_b] & """)" DoCmd.OpenForm stDocName, , , stLinkCriteria The result is an error diolog: Microsoft Office Access can't find the field '|' referred to in your expression. field_a and field_b are the 'keys' f...

Charting blocks of time as a bar graph
Is there a way to convert/display a given chunk of time (for example a person's shift) into a bar graph/chart? For example: If someone works 6am-2pm, I would like to have that shift show up as a bar in a graph covering those hours. I would ultimately like to enter a person's shift and have it display somewhere else on the page or another sheet in a graph form to see graphically if we are short in coverage somewhere. An example of what I mean can be found here: https://public.me.com/gadgetman Any suggestions/help???? hi, you can download a model (Gantt Chart) here: http://o...

User Interface stops working
Hi, I am developing an application using MFC in MS Visual Studio 2005. I created a single dialog based MFC application. The framework generates the code required to create the single dialog box. In the InitInstance() function, the dialog box is created using DoModal() to create a modal dialog box. I have added a tab control in the main dialog box. I have 5 tabs. For each tab, I create a dialog box using MFC. Each dialog box contains various buttons and controls. One dialog box contains an ActiveX control object. When a particular tab is selected by the user, the corresponding dialog ...

Time #2
I need to be able to click on a cell and have it be populated with the current HH:MM:SS. I know ctrl+shift+; will do the job but I need it to be even more simple than that for the end users. One way: Put this in the worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With Range("J1") If Not Intersect(Target, .Cells) Is Nothing Then .Value = Time .NumberFormat = "HH:MM:SS" End If End With End Sub ...

Scaling x-axis with time
Can anyone help me with a problem I am having? I have a spreadsheet showing 2000 data points plotted against time, sounds simple However, the number of data points corresponding to 1 second of 'footage' varies. E.g. in a 1 second interval, I sometimes get 5 readings being recorded (0.2, 0.4, 0.6, 0.8, 1.0 sec), and in the next 1 second interval, I get 10 readings being recorded (0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0 sec). When it comes to graphing the data, the time scale along the X-axis is not 'to scale', I am looking to have the tick mark labels to be di...

Queries not working after compiling the DB
I have built a Database and spilitted into FE & BE. in the FE i got 2 forms that are based on queries and used for searching. I made MDE file and used startup option to remove admin options so noone can change the DB. Now when i copy the MDE file on different PCs on network to access the BE on server I have noticed that Search option that are based on the queries is not working. Any Idea? while the same file is working fine on my computer. I have made sure everything else is fine. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200705/1...

How can i insert multiple pictures in a word doc, simultaneously?
Hello! I need to put in multiple photos from a file in my reports, and i would like them to be sized to fit 6-10 per page. Is there any way to do that in one simple step? Thanks! It depends on your definition of simple. In order to control the layout of the images you need to insert them in a table of fixed cell width. The images will then adapt to the width of the cell. How many will fit of a page is determined by whether the images are portrait mode or landscape mode - or mixed modes. The following macro will insert a two column table at the cursor and proceed to en...

Pivot: grand totals
Hi all, i need your help for the below table: ITEMS YEAR YRLY_TOT GRAND_TOT 2008 $10 1. ITEM1 2009 $20 $60 2010 $30 2008 $10 2. ITEM2 2009 $30 $80 2010 $40 2008 $20 3. ITEM3 2009 $30 $90 2010 $40 Each item has yearly totals (column YRLY_TOT). I want column GRAND_TOT to show total of all three years for each item ( i guess the three cells shou...

How do I keep the help window on top and work on my worksheet
When working on a sheet in excel, I open the help window and wish to read it whilst making a suitable entry on the page. The help window closes as soon as it loses focus. In earlier versions this did not happen. Is there a fix for it? If you are using Excel 2003, the top left corner of the help-presented window has a Tile/Untile option. Not really pretty but does enable you to read the help whilst working on the sheet. Howard Walker 635 Wrote: > When working on a sheet in excel, I open the help window and wish to > read it > whilst making a suitable entry on the page. The hel...

How do I calculate checked boxes in a report or query?
I have created a table and a form with yes/no checked boxes, now I'd like to make a report or a query and in this I'd like to calculate how much was done (according to a checked box) from a respective year. How can I do this? Use date criteria and this for the count --- Sum(Abs(Nz([YourCheckBox],0))) -- KARL DEWEY Build a little - Test a little "leona" wrote: > I have created a table and a form with yes/no checked boxes, now I'd like to > make a report or a query and in this I'd like to calculate how much was done > (according to a checked box) fr...

Vacation/Sick Time and Project Accounting
Is there a way we can automatically update the accrued vacation/sick time in Payroll by entering a non-billable project number in Project Accounting and set the pay code to VACN or SICK in the timesheet entry form? ...