Calulating the total time of vairous events (dates) happened in various periods (Dates)
Calulating the total time of vairous events (dates) happened in various periods (Dates)
(OP)
Hi,
I really appreciate if I can get some help on this.
I have 300 events (list A) with start and stop dates&time in the format of dd/mm/yyyy hh.mm.ss, and they never over lap.
I have another list of events (list B)with start and stop times in same format in list B.
What I want to find out is the total days, hr and minutes when the events of both lists occur at the same time.
It sound very easy to do but I just can't think round it.
Could anyone help me on this please?
I attached an example of the lists.
Thanks in advance!
Felix
I really appreciate if I can get some help on this.
I have 300 events (list A) with start and stop dates&time in the format of dd/mm/yyyy hh.mm.ss, and they never over lap.
I have another list of events (list B)with start and stop times in same format in list B.
What I want to find out is the total days, hr and minutes when the events of both lists occur at the same time.
It sound very easy to do but I just can't think round it.
Could anyone help me on this please?
I attached an example of the lists.
Thanks in advance!
Felix





RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
I'll ignore the difference in date formats; One possibility is to tag the two lists with a third column containing A or B as required, combine them and sort them by start date. A new column tests whether two sequential tags are different. I expect that another column or so would test for overlaps
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
I do have project as well, can you tell me how should I be using in project to calculate the total time I need?
Many Thanks
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
I did not check carefully, but at appears that your dates are stored as dates, not as plain text. I can subtract the first cell from the second cell and get an intelligent answer.
Did you try reformatting the answer cell? All of your elapsed times appears to be hours and minutes. You don't need days and years.
Excel may be doing something weird with years.
--
JHG
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
Oops! The best format actually is hours, minutes and seconds, hh:mm:ss.
--
JHG
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
I think you might have misunderstood me.
I need to find the total duration when the events in list A happen at the same time as the events in list B.
Not the duration of the events within the lists.
Felix
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
In Project, you would simply assign the same resource to all the tasks and see where that single resource is overallocated, since that resource would need to be doing double work. I'm not sure whether Project will deal well with 1 minute scales, though.
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
Each event = Task
Each list = Resource
Somewhere there are reports that show resource loading and double-booking of resources.
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
You have four types of times: Astarts, Astops, Bstarts, and Bstops.
» Create a single columnar list that merges all your recorded times in one column, with the type of time flagged in the adjacent column.
» Sort this pair of columns into time order (ascending).
» Create four more adjacent columns, one for the running count of Astart times, one for the running count of Astop times, one for the running count of Bstart times, and one for the running count of Bstop times.
» At this stage, for every row, Astart should either be equal to Astop or one greater than it, and ditto Bstart versus Bstop. If not, your data is corrupted.
» Now you have verified the logical integrity of the data, each row represents the start of a period of time during which the situation does not change. If Astart=Astop then your event A is NOT happening during that period, and if Bstart=Bstop then your event B is NOT happening.
» The rest of your exercise should be pretty straight forward.
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
Index 1 finds the last Stop Time in List A that is earlier than the List B Start Time
=MATCH(E3,$C$3:$C$59)
Index 2 finds the last Start Time in List A that is earlier than the List B Stop Time
=MATCH(F3,$B$3:$B$59)
Start Ovelap is the later of the List B Start, or the next List A start after Index 1
=MAX(E3,INDEX($B$3:B59,H3+1))
End Ovelap is the earlier of the List B End, or the List A end at Index 2
=MIN(F3,INDEX($C$3:$C59,I3))
Duration is then just End - Start, multiplied by 24 x60 if you want it in minutes
Note that it is assumed that List B times never overlap with more than one List A time.
If they do, it gets a bit more complicated.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
where StartA refers to the range B3:B59 - start time of List A. this formula will find an index of List A matching start time in List B (E3) or will display #N/A if no match found. then use INDEX function to display Start and Stop times. The new table will contain only mathching rows of two lists. See attached file.
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
IDS,
Unfortunately, List B times DO overlap with more than one List A time, hence I have been scratching my head for the past days to calculate the time. Could you suggest any development I could do on your spreadsheet to achieve so?
Many THanks
Felix
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
If you are relying on the results please check carefully, because I haven't!
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
So did mine, but presumably his actual data has (or may have) cases where there are overlaps over more than one block.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
The code below finds 23 overlapping A/B events and it will also tell you which A functions overlaps which B event (partial and or fully - thats not identified)
Public Function findoverlap()
Dim Astarttime, Bstarttime, Aendtime, Bendtime As Date
Dim overlap, n As Integer
overlap = 0
n = 0
Do
Range("b3").Offset(n, 0).Select
If IsEmpty(ActiveCell.Value) Then
Exit Do
End If
Astarttime = ActiveCell.Value
Aendtime = ActiveCell.Offset(0, 1).Value
Range("e3").Select
Do
'ActiveCell.Offset(0, 3).Select
Bstarttime = ActiveCell.Value
Bendtime = ActiveCell.Offset(0, 1).Value
If Astarttime > Bstarttime And Astarttime < Bendtime Then
overlap = overlap + 1
ActiveCell.Offset(0, 2).Value = n + 3
Exit Do
End If
If Aendtime > Bstarttime And Astarttime < Bstarttime Then
overlap = overlap + 1
ActiveCell.Offset(0, 2).Value = n + 3
Exit Do
End If
If Aendtime < Bstarttime Then
ActiveCell.Offset(1, -3).Select
Exit Do
End If
ActiveCell.Offset(1, 0).Select
If IsEmpty(ActiveCell.Value) Then
Exit Do
End If
Loop
n = n + 1
Loop
Range("a1") = overlap
End Function
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)
Thank you very very much for all the help and suggestions you have listed above.
I am very grateful, and problem is solved!
Many Thanks again!
RE: Calulating the total time of vairous events (dates) happened in various periods (Dates)