INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

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: 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)

(OP)
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

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

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

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

RE776,

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)

(OP)
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

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

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/getfile.aspx?folder=7...


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)

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.

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

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.

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

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/

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

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.

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

(OP)
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

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

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/

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

Quote:

?? 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/

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

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

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

(OP)
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!

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close