seanpark22
New Member
- Joined
- Mar 16, 2017
- Messages
- 4
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
<tbody>
</tbody>
TABLE 2
<tbody>
</tbody>
Feel free to ask question, if you may have.
Anticipating a quick solution for this riddle.
Thanks heaps.
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
Employee | Trip |
<colgroup><col width="93"></colgroup><tbody> </tbody> | End Date | |||
<colgroup><col width="81"></colgroup><tbody> </tbody> | Trip# 1 |
<colgroup><col width="81"></colgroup><tbody> </tbody> |
<colgroup><col width="81"></colgroup><tbody> </tbody> | |||
<colgroup><col width="81"></colgroup><tbody> </tbody> | Trip# 2 |
<colgroup><col width="81"></colgroup><tbody> </tbody> |
<colgroup><col width="81"></colgroup><tbody> </tbody> |
<tbody>
</tbody>
TABLE 2
<colgroup><col width="93"></colgroup><tbody> </tbody> | Start Date | End Date | ||
A |
<colgroup><col width="93"></colgroup><tbody> </tbody> |
<colgroup><col width="93"></colgroup><tbody> </tbody> | ||
B |
<colgroup><col width="93"></colgroup><tbody> </tbody> |
<colgroup><col width="93"></colgroup><tbody> </tbody> | ||
C |
<colgroup><col width="93"></colgroup><tbody> </tbody> |
<colgroup><col width="93"></colgroup><tbody> </tbody> | ||
D |
<colgroup><col width="93"></colgroup><tbody> </tbody> |
<colgroup><col width="93"></colgroup><tbody> </tbody> |
<tbody>
</tbody>
Feel free to ask question, if you may have.
Anticipating a quick solution for this riddle.
Thanks heaps.