compair scheduled hours between 2 people

webejamn

New Member
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>

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Robert Mika

MrExcel MVP
What result do you expect in the first few lines?

webejamn

New Member
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.

MikeWx

Active Member
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>

 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

Replies
6
Views
148
Replies
2
Views
192
Replies
19
Views
395
Replies
5
Views
112
Replies
3
Views
120

1,129,684
Messages
5,637,790
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back