My data set has Name, Assignment, Start Date, Start Time, End Date and End Time as well as a field that has the calculated hours worked. Under the Assignment field there are 5 categories. I need a formula that will give me: If assignment #1 AND assignment #2 were covered each day during the same 4 hour period. One more piece of information -- there is no set start time and end time so I would need to find a 4 hour overlap.
For example:
<tbody>
</tbody>
For example:
Lname | Fname | Assignment | StartDate | StartTime | EndDate | EndTime | Hours |
Doe | Cathy | Assignment2 | 1/2/2014 | 8:00 | 1/2/2014 | 23:55 | 16 |
Black | Aaron | Assignment1 | 1/2/2014 | 19:30 | 1/2/2014 | 23:55 | 4 |
May | April | Assignment1 | 1/2/2014 | 17:15 | 1/3/2014 | 0:00 | 7 |
Dunn | Steve | Assignment4 | 1/2/2014 | 20:00 | 1/3/2014 | 1:30 | 6 |
Pen | John | Assignment1 | 1/4/2014 | 8:00 | 1/5/2014 | 7:00 | 23 |
Mann | George | Assignment3 | 1/4/2014 | 8:00 | 1/5/2014 | 7:30 | 24 |
Doer | Jane | Assignment2 | 1/4/2014 | 16:00 | 1/5/2014 | 7:00 | 15 |
Dunn | Steve | Assignment4 | 1/4/2014 | 20:00 | 1/5/2014 | 7:30 | 12 |
Doe | Cathy | Assignment3 | 1/6/2014 | 17:30 | 1/6/2014 | 22:30 | 5 |
Smith | Jim | Assignment4 | 1/6/2014 | 18:00 | 1/6/2014 | 22:00 | 4 |
Taylor | Mike | Assignment2 | 1/6/2014 | 18:00 | 1/6/2014 | 22:00 | 4 |
Pen | John | Assignment1 | 1/7/2014 | 17:30 | 1/7/2014 | 23:30 | 6 |
Doe | Cathy | Assignment2 | 1/7/2014 | 17:30 | 1/7/2014 | 22:00 | 5 |
Mann | George | Assignment3 | 1/7/2014 | 17:30 | 1/7/2014 | 23:30 | 6 |
May | April | Assignment4 | 1/7/2014 | 16:30 | 1/7/2014 | 23:30 | 7 |
<tbody>
</tbody>