Count Employee Work Time - Don't Double-count Overlapping Apts.
 

From a database of customer training appointments, I need to count the time
each employee works each day. The hard part for me is that more than one
customer is scheduled with one employee at a time. My current formula
double-counts overlapping time periods.

Current formula:
=(SUMPRODUCT((Employee=382)*(Appt_Date=4/1/2006)*(Duration)))/60

The data looks like:
employee Appt_Date app start time Duration (min.) Correct
Sum
382 4/1/2006 9:00:00 AM 60
382 4/1/2006 9:00:00 AM 30 60
382 4/1/2006 1:00:00 PM 90
382 4/1/2006 2:00:00 PM 60 180
382 4/1/2006 4:00:00 PM 60
382 4/1/2006 4:00:00 PM 60 60

300 Total

What formula would avoid double counting the same time period(s). I can add
columns to the right of the data combining the date and start time or
calculating the end time.

Suggestions?

Thanks,

J