Excel Facetime Measurement

S

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%.

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: