J
J
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
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