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