compair scheduled hours between 2 people

webejamn

New Member
Joined
Jun 14, 2012
Messages
3
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:005:0014:005:0014:005:0014:005:0014:0045:00:00#
Hours Together
% of time
5:00
15:455:0015:455:0015:307:0017:0042:00:00
6:0017:006:0017:006:0017:007:0017:0043:00:00
6:3017:006:0017:006:3017:007:0017:0042:00:00
6:3017:006:0017:006:3017:007:0017:0042:00:00
6:3017:006:0017:006:3017:007:0017:0042:00:00
6:3017:006:0017:006:3017:007:0017:0042:00:00
7:0017:007:0017:007:0017:007:0017:0040:00:00
5:0013:305:0013:305:0013:305:0013:3034:00:00
6:0016:306:0016:306:0016:306:0016:3042:00:00
6:0014:306:0014:306:0014:306:0014:307:0015:3042:30:00
5:0014:305:0014:305:0014:305:0014:308:0012:0042:00:00
6:0015:306:0015:306:0015:306:0015:309:0013:0042:00:00
5:0013:45
5:0013:455:0013:455:0013:455:009:0039:00:00
5:0013:305:0013:305:0013:305:0013:305:0013:3042:30:00
5:0013:305:0013:305:0013:305:0013:305:0013:3042:30:00
5:0013:305:0013:305:0013:305:0013:305:0013:3042:30:00
5:0013:305:0013:305:0013:305:0013:305:0013:3042:30:00
5:0013:455:0013:455:0013:455:0013:455:0013:4543:45:00
5:0013:305:0013:305:0013:305:0013:305:0013:3042:30:00
6:0013:306:0013:306:0013:306:0013:306:0013:3037:30:00
6:0013:306:0013:306:0012:006:0013:306:0013:3036:00:00
8:0015:308:0015:008:0015:008:0015:008:0015:0035: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.

webejamn

New Member
Joined
Jun 14, 2012
Messages
3
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

Active Member
Joined
Jan 16, 2010
Messages
436
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

*ABCDEFGHIJKLMNOPQR
15:0014:005:0014:005:0014:005:0014:005:0014:00****TotalHours Together% of time# of days
25:0015:455:0015:45****5:0015:307:0017:00**42:0027:0064.29%3
36:0017:006:0017:00****6:0017:007:0017:00**43:0024:0055.81%3
45:0013:305:0013:305:0013:30**5:0013:30****34:0034:00100.00%4
55:0013:455:0013:455:0013:455:0013:455:009:00****39:0039:00100.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
CellFormula
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top