Excel Facetime Measurement

[Search & You Will Find]

What I am looking to do is calculate time between two individuals and
determine how much of the day (percentage) they will see each other.
Example: Employee A is at work from 8-5 and employee B is at work
from
12-9 then they would have 5 hours of facetime which would be 62.5%
facetime.

My Workbook is set up like this:


Worksheet A
Employee | Monday Start | Monday End | Tuesday
Start |
Employee 1 | 10:00 AM | 8:00 PM | 10:00
AM |


Worksheet B
Employee | Monday Start | Monday End | Tuesday
Start |
Employee A | 8:00 AM | 5:00 PM | 8:00
AM |
Employee B | 8:00 AM | 5:00 PM | 8:00
AM |
Employee C | 8:00 AM | 5:00 PM | 8:00
AM |
Employee D | 8:00 AM | 5:00 PM | 8:00
AM |
etc


At the end of the row for employee A is where I'd like to put the
calculation. I was thinking something down the lines of a SUMPRODUCT,
but I don't think it will work right with the situation I have.


So for Monday, facetime is xx hours; Tuesday, facetime is xx hours;
etc. and the forumla would add them up, determine how many hours out
of 40 they align, and give the total facetime of xx.xx%.

 

[James Ravenswood]

What I am looking to do is calculate time between two individuals and
determine how much of the day (percentage) they will see each other.
Example: Employee A is at work from 8-5 and employee B is at work
from
12-9 then they would have 5 hours of facetime which would be 62.5%
facetime.

My Workbook is set up like this:

Worksheet A
Employee      |   Monday Start    |     Monday End    |     Tuesday
Start     |
Employee 1   |   10:00 AM          |     8:00 PM          |      10:00
AM          |

Worksheet B
Employee      |   Monday Start    |     Monday End    |     Tuesday
Start     |
Employee A   |   8:00 AM           |     5:00 PM          |      8:00
AM            |
Employee B   |   8:00 AM           |     5:00 PM          |      8:00
AM            |
Employee C   |   8:00 AM           |     5:00 PM          |      8:00
AM            |
Employee D   |   8:00 AM           |     5:00 PM          |      8:00
AM            |
etc

At the end of the row for employee A is where I'd like to put the
calculation. I was thinking something down the lines of a SUMPRODUCT,
but I don't think it will work right with the situation I have.

So for Monday, facetime is xx hours; Tuesday, facetime is xx hours;
etc. and the forumla would add them up, determine how many hours out
of 40 they align, and give the total facetime of xx.xx%.

See:

http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/3e33584cf059de56#