I need to make a worksheet that will track time spent on a certain task. I need to be able to calculate time spent but some times may overlap. For instance:
9:15 - 9:30 15 minutes
9:20 - 11:00 100 inutes
9:25 - 10:00 35 minutes
If I add all of these up it would be a total of 150 minutes but since some of them overlapped the actual time spent was 105 minutes.
I hope that makes sense. It seems like it should be fairly simple to do but I just can't seem to make it work.
Welcome to the forum.
If there are no gaps, how about =MAX(B1:B3)-MIN(A1:A3)
Entia non sunt multiplicanda sine necessitate
If hours and minutes will work,
Column A Start Time, Column B Stop Time, Column C Total Time.
Column C =B1-A1
AutoSum Column C which should return something like 2:30
Thanks guys but neither of those will do what I need.
shg, There are gaps. If there were no gap I wouldn't need a spreadsheet I would just use the beginning and ending hours and that would be all I would need.
Stupidav, This would give me the total of the hours, I need to exclude the times that overlap.
Maybe a better explanation is needed. I am monitoring time spent working electrical outages in a Dispatch center. There may be multiple outages occurring at the same time. There are also gaps in time where there are no outages. I can pull a report that has my total outage hours on it but that does not give me what I need. Example: A dispatcher could have 3 outages in a one our period. One of the outages could have been the full hour long and the other two may have been 15 and 30 minutes which would be 1 hr and 45 minutes of outage time but the Dispatcher only worked one hour.
Better still post a sample file ... a good sample file will generally account for half a dozen posts.Originally Posted by mountaincruiser
In the sample ensure the data accurate reflects your real file in terms of ranges used, data types etc... also outline the expected results as this helps people validate logic.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I have attached a file. The highlighted cells are the ones that I need to track. I can easily add up the total # of outage hours. Where I am having a problem is excluding overlapping times.
These types of things can get quite complicated using native formulae especially if as is the case here the data is not sorted by Start.
Can you confirm expected output given sample - I think I calculated at 55:58:29 but I didn't spend long working through it.
Also, to be clear - this is not a "simplified" version of your real file ?
I ask because often "outage" analysis tends to be based on multiple factors - eg machine 1 & machine 2 being down simultaneously.
This means there is often there are additional clauses determining which datetime values are included in analysis - and if that's the case here it would be good to know now.
I assigned the outage events = -1 and the online events as =+1, then commingled the times and sorted to arrive at the same answer as DO:
DO, how did you do it?
Please Login or Register to view this content.
This could be done via a UDF rather than needing to rearrange the data.
Last edited by shg; 10-08-2010 at 11:21 AM.
It seems the data is sorted by OutageRecID and on occasion it seems this can be out of sequence regards time (A6/A5, A19/A18 etc)
If the data (A:F) is sorted by Column A (Start) rather than by OutageRecID then:
result being sum of J2:J41Please Login or Register to view this content.
If Duration of any given outage can exceed 24 hours avoid using TIME in Column I, rather use:
Please Login or Register to view this content.
That is so simple; very nicely done.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks