compair scheduled hours between 2 people

[webejamn]

I have tried many formulas, they all fail somewhere, i am beyond my abilities now...
i need to compare the bolded schedule to the other schedules, to give a % of hours worked together. does that make since? i am tying to see the number of hours and the % of time each employee spends with their managers. Now that im typing this, it would be nice to see that number of days they spend at work as well.

Total hours staffed

5:00

14:00

5:00

14:00

5:00

14:00

5:00

14:00

5:00

14:00

45:00:00#

Hours Together

% of time

5:00

15:45

5:00

15:45

5:00

15:30

7:00

17:00

42:00:00

6:00

17:00

6:00

17:00

6:00

17:00

7:00

17:00

43:00:00

6:30

17:00

6:00

17:00

6:30

17:00

7:00

17:00

42:00:00

6:30

17:00

6:00

17:00

6:30

17:00

7:00

17:00

42:00:00

6:30

17:00

6:00

17:00

6:30

17:00

7:00

17:00

42:00:00

6:30

17:00

6:00

17:00

6:30

17:00

7:00

17:00

42:00:00

7:00

17:00

7:00

17:00

7:00

17:00

7:00

17:00

40:00:00

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

34:00:00

6:00

16:30

6:00

16:30

6:00

16:30

6:00

16:30

42:00:00

6:00

14:30

6:00

14:30

6:00

14:30

6:00

14:30

7:00

15:30

42:30:00

5:00

14:30

5:00

14:30

5:00

14:30

5:00

14:30

8:00

12:00

42:00:00

6:00

15:30

6:00

15:30

6:00

15:30

6:00

15:30

9:00

13:00

42:00:00

5:00

13:45

5:00

13:45

5:00

13:45

5:00

13:45

5:00

9:00

39:00:00

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

42:30:00

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

42:30:00

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

42:30:00

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

42:30:00

5:00

13:45

5:00

13:45

5:00

13:45

5:00

13:45

5:00

13:45

43:45:00

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

5:00

13:30

42:30:00

6:00

13:30

6:00

13:30

6:00

13:30

6:00

13:30

6:00

13:30

37:30:00

6:00

13:30

6:00

13:30

6:00

12:00

6:00

13:30

6:00

13:30

36:00:00

8:00

15:30

8:00

15:00

8:00

15:00

8:00

15:00

8:00

15:00

35:30:00

<colgroup><col span="9"><col><col span="4"><col><col><col><col></colgroup><tbody>
</tbody>

 

[Robert Mika]

What result do you expect in the first few lines?

 

[webejamn]

I am not 100% sure what your asking, i am assuming i didnt give enough info, sorry if this is the case,
top schedule is one of my managers, and the following are their employees. Mon-Sun. this specific unit does not have sunday schedules. i have calculated the number of staffed hours. per person. the next column i would like to see the number of hours the manager shares with the employee, then i would like to see the % of the employees staffed time with the manager, and i was thinking an additional column showing the number of shared days would be cool too.

please let me know if you need additional info.

 

[MikeWx]

There is probably a more elegant solution with VBA. This "brute force" formula based solution will work as long as the schedules don't go over midnight. Columns O and P are formatted as [h]:mm, Column Q is formatted as Percentage, and Column R is formatted as General. The asterisks below are really blanks as in your original data above.

Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

5:00

14:00

5:00

14:00

5:00

14:00

5:00

14:00

5:00

14:00

*

*

*

*

Total

Hours Together

% of time

# of days

2

5:00

15:45

5:00

15:45

*

*

*

*

5:00

15:30

7:00

17:00

*

*

42:00

27:00

64.29%

3

3

6:00

17:00

6:00

17:00

*

*

*

*

6:00

17:00

7:00

17:00

*

*

43:00

24:00

55.81%

3

4

5:00

13:30

5:00

13:30

5:00

13:30

*

*

5:00

13:30

*

*

*

*

34:00

34:00

100.00%

4

5

5:00

13:45

5:00

13:45

5:00

13:45

5:00

13:45

5:00

9:00

*

*

*

*

39:00

39:00

100.00%

5

<COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 32px"><COL style="WIDTH: 39px"><COL style="WIDTH: 32px"><COL style="WIDTH: 39px"><COL style="WIDTH: 32px"><COL style="WIDTH: 39px"><COL style="WIDTH: 32px"><COL style="WIDTH: 39px"><COL style="WIDTH: 32px"><COL style="WIDTH: 39px"><COL style="WIDTH: 32px"><COL style="WIDTH: 39px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 39px"><COL style="WIDTH: 103px"><COL style="WIDTH: 66px"><COL style="WIDTH: 61px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula O2 {=SUM((MOD(COLUMN(A2:N2),2)=0)*A2:N2)-SUM((MOD(COLUMN(A2:N2),2)=1)*A2:N2)} P2 =IF(ISNUMBER(B$1),IF(ISNUMBER(B2),MIN(B$1,B2)-MAX(A$1,A2),0),0)+IF(ISNUMBER(D$1),IF(ISNUMBER(D2),MIN(D$1,D2)-MAX(C$1,C2),0),0)+IF(ISNUMBER(F$1),IF(ISNUMBER(F2),MIN(F$1,F2)-MAX(E$1,E2),0),0)+IF(ISNUMBER(H$1),IF(ISNUMBER(H2),MIN(H$1,H2)-MAX(G$1,G2),0),0)+IF(ISNUMBER(J$1),IF(ISNUMBER(J2),MIN(J$1,J2)-MAX(I$1,I2),0),0)+IF(ISNUMBER(L$1),IF(ISNUMBER(L2),MIN(L$1,L2)-MAX(K$1,K2),0),0)+IF(ISNUMBER(N$1),IF(ISNUMBER(N2),MIN(N$1,N2)-MAX(M$1,M2),0),0) Q2 =P2/O2 R2 =IF(ISNUMBER(B$1),IF(ISNUMBER(B2),IF(MIN(B$1,B2)-MAX(A$1,A2)>0,1,0),0),0)+IF(ISNUMBER(D$1),IF(ISNUMBER(D2),IF(MIN(D$1,D2)-MAX(C$1,C2)>0,1,0),0),0)+IF(ISNUMBER(F$1),IF(ISNUMBER(F2),IF(MIN(F$1,F2)-MAX(E$1,E2)>0,1,0),0),0)+IF(ISNUMBER(H$1),IF(ISNUMBER(H2),IF(MIN(H$1,H2)-MAX(G$1,G2)>0,1,0),0),0)+IF(ISNUMBER(J$1),IF(ISNUMBER(J2),IF(MIN(J$1,J2)-MAX(I$1,I2)>0,1,0),0),0)+IF(ISNUMBER(L$1),IF(ISNUMBER(L2),IF(MIN(L$1,L2)-MAX(K$1,K2)>0,1,0),0),0)+IF(ISNUMBER(N$1),IF(ISNUMBER(N2),IF(MIN(N$1,N2)-MAX(M$1,M2)>0,1,0),0),0) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



Mike