Excel Employee vs Seminar Problem

[seanpark22]

Hi Champs,

I need some urgent help to sort out data for my organization.

Basically, we have employees who go on official trips mostly for various client locations, seminars, courses etc. Here I need to find number of days employees spent under each seminar.

The data looks like as mentioned below. Table 1: Employee Trips and Table 2: Seminar Schedule.

Here, when ever employees' trips (start/end dates) lie within a seminar start/end dates, I used an array formula to find which seminar did the employee attended (quite straight forward).

However, my formula fails when employee's trip (start/end dates) fall among more than one seminars.

For instance:
Employee XYZ had Trip 1 from 30-dec-2015 to 5-Jan-2016.
However, Seminars he was eligible had Table 2 listings. Here, you can see the his trip dates fall in more than 1 seminars. In this case, his 1st trip falls 2 days in seminar A and 5 days in seminar B.

So, similarly I need to find out number of days employees spent in various seminars during their trips.
Remember, I need to find both 'number of days' and 'seminar name'

TABLE 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee
[/TD]
[TD]Trip
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl72, width: 93"]Start Date
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]End Date
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 81"]
<colgroup><col width="81"></colgroup><tbody>[TR]
[TD="class: xl71, width: 81"]Mr. XYZ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Trip# 1
[/TD]
[TD][TABLE="width: 81"]
<colgroup><col width="81"></colgroup><tbody>[TR]
[TD="class: xl71, width: 81"]30-Dec-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<colgroup><col width="81"></colgroup><tbody>[TR]
[TD="class: xl71, width: 81"]5-Jan-16
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 81"]
<colgroup><col width="81"></colgroup><tbody>[TR]
[TD="class: xl71, width: 81"]Mr. XYZ[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Trip# 2
[/TD]
[TD][TABLE="width: 81"]
<colgroup><col width="81"></colgroup><tbody>[TR]
[TD="class: xl71, width: 81"]2-Jul-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<colgroup><col width="81"></colgroup><tbody>[TR]
[TD="class: xl71, width: 81"]5-Jul-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


TABLE 2
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl72, width: 93"]Seminar[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl71, width: 93"]1-Feb-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl71, width: 93"]31-Dec-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl71, width: 93"]1-Jan-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl71, width: 93"]21-Mar-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl71, width: 93"]6-Jun-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl71, width: 93"]4-Jul-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl71, width: 93"]5-Jul-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<colgroup><col width="93"></colgroup><tbody>[TR]
[TD="class: xl71, width: 93"]31-Dec-16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Feel free to ask question, if you may have.

Anticipating a quick solution for this riddle.

Thanks heaps.

 

[seanpark22]

Table 1
[TABLE="width: 324"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Employee[/TD]
[TD]Trip[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Mr. XYZ[/TD]
[TD]Trip# 1[/TD]
[TD]30-Dec-15[/TD]
[TD]5-Jan-16[/TD]
[/TR]
[TR]
[TD]Mr. XYZ[/TD]
[TD]Trip# 2[/TD]
[TD]2-Jul-16[/TD]
[TD]5-Jul-16[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="width: 279"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Seminar[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1-Feb-15[/TD]
[TD]31-Dec-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1-Jan-16[/TD]
[TD]21-Mar-16[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]6-Jun-16[/TD]
[TD]4-Jul-16[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]5-Jul-16[/TD]
[TD]31-Dec-16[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...225379-excel-employee-vs-seminar-problem.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.