Calculate Face Time

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

For a pair of individuals, the overlap time is the minimum of the end
times less the maximum of the start times. So if A1 thru B2 contain:

8:00 AM 5:00 PM
12:00 PM 9:00 PM


then =MIN(B1,B2)-MAX(A1,A2) will display the 5 hours
and =(MIN(B1,B2)-MAX(A1,A2))/(B1-A1) formatted as % will display
55.56%

You might be able to adapt this method to your data layout.

 

[Search & You Will Find]

For a pair of individuals, the overlap time is the minimum of the end
times less the maximum of the start times.  So if A1 thru B2 contain:

8:00 AM 5:00 PM
12:00 PM        9:00 PM

then =MIN(B1,B2)-MAX(A1,A2)  will display the 5 hours
and  =(MIN(B1,B2)-MAX(A1,A2))/(B1-A1) formatted as % will display
55.56%

You might be able to adapt this method to your data layout.- Hide quoted text -

- Show quoted text -

Thank you for this response. It worked PERFECTLY until there were days
that individual a worked, but individual b didn't work. :-/
I am trying to avoid a compound IF phrase that looks at each persons
days off as that would become way to combersome of a formula. Any
other ideas?

 

[Puppet_Sock]

On Dec 6, 11:07 am, "Search & You Will Find" <[email protected]>
wrote:
[snip previous]

Thank you for this response. It worked PERFECTLY until there were days
that individual a worked, but individual b didn't work. :-/
I am trying to avoid a compound IF phrase that looks at each persons
days off as that would become way to combersome of a formula. Any
other ideas?

An extra column thrown in there will often allow you to avoid a huge
amount of complexity in an if( ) description. So the inner part of
some
huge complicated if( ) becomes a new column. Then the if( ) can
reference that column. It also gives you a chance to put a column
heading on, producing some extra documentation of what you are
doing. If well chosen, the extra column may help in other ways.

An other possible extra column use is something like total hours for
the day. You can maybe use that to simplify the if( ) formulas.
Socks

 

[James Ravenswood]

Thank you for this response. It worked PERFECTLY until there were days
that individual a worked, but individual b didn't work. :-/
I am trying to avoid a compound IF phrase that looks at each persons
days off as that would become way to combersome of a formula. Any
other ideas?

The IF logic can be consolidated into a single test if 00:00 is not a
valid start/end time:

=IF(A1*B1*A2*B2=0,0,posted formula)