Calulating the total time of vairous events (dates) happened in various periods (Dates) 2

[RE776]

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

 

[IRstuff]

That's probably because Excel, which could possibly do this, is an ugly tool for doing this. Project would be a much better tool for doing this because that's one of the built in functionalities.

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

Need help writing a question or understanding a reply? forum1529

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!

 

[RE776]

Thanks for the Reply IRStuff.

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

 

[drawoh]

RE776,

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

 

[drawoh]

RE776,

Oops! The best format actually is hours, minutes and seconds, hh:mm:ss.

--
JHG

 

[RE776]

Hi Drawoh,

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

 

[IRstuff]

Attached is a stab at finding 1 of the 3 possible cases, that the second task is fully contained within the duration of the previous task that's of a different category. I don't think that your example had any of the other two cases, but this is a start.

http://files.engineering.com/getfil...bb6-4a5c-a308-12a763e29de6&file=Example2.xlsx

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

Need help writing a question or understanding a reply? forum1529

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!

 

[MintJulep]

I agree with IRStuff. You could easily co-opt Project to do this.

Each event = Task

Each list = Resource

Somewhere there are reports that show resource loading and double-booking of resources.

 

[Denial]

I've never used Project, so I'd be stuck with Excel, and I'd proceed as follows.
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.

 

[IDS]

I don't think it's too bad doing it in Excel. I have attached a modified copy of the spreadsheet that will do the job for the times as listed.

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/

 

[yakpol]

I believe Excel alone can handle this. For each item on list B enter formula = match(E3, startA,0).
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.

 

[RE776]

Dear all Thanks very much for your help!

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

 

[IDS]

OK, I have modified it so it will work with up to 3 overlaps. You could increase that indefinitely by copying columns O and P across as far as required, then modifying the sum formula to include each overlap.

If you are relying on the results please check carefully, because I haven't!

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

?? The sheet I posted found all the overlaps in your original sheet

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!

 

[IDS]

?? The sheet I posted found all the overlaps in your original sheet

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/

 

[MortenA]

I could resist´:

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

 

[RE776]

Dear all,

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!

 

[IFRs]

RE776 - it is customary to star the post(s) that helped you...