Complicated shift differential question.

your requirement and data definition is not clear, what do you mean by "Shift differential", can you post some sample data with the expected result??

sanrv1f said:

your requirement and data definition is not clear, what do you mean by "Shift differential", can you post some sample data with the expected result??

Click to expand...

Try using excel jeanie

http://www.excel-jeanie-html.de/index.php?f=1

well by shift differential I mean two different sets of time that I need to count for . By that I have a

START TIME B3
END TIME B4

The differential meanin I want to count the hours that I work in the differential period, such as first differential is from 14:00-23:00

Also I have another differential period from 22:00-07:00, however if you start at 22:00 I don't want it to count from 22:00-23:00 if started at 14:00 should only count the hours 23:00, and then count the hours from 23:00-07:00...

It just needs to be able to count those two periods of time.

I have been able to do a formula to count from 22:00-07:00 with the help of a friend, but he was not able to figure out from 14:00-23:00.

=MOD(B4-B3,1)-(B3>B4)*MEDIAN(0,B4-7/24,4.5/12)-MAX(0,MIN(22/24,B4+(B3>B4))-MAX(7/24,B3))

Don't know if anyone can adjust that to match the time from 14:00-23:00; if so then the only problem that I will really have is excluding the 1 hour differential that would get included in the count if you started at 14:00 and worked till 23:00 because then its going to return, 9 hours worked, and 1 hour worked in the period of 22:00-07:00

any ideas or help, even if someone could modify the formula,
to count the hours from 14:00 - 23:00. I could adjust the rest of the sheet myself.

=MOD(B4-B3,1)-(B3>B4)*MEDIAN(0,B4-7/24,4.5/12)-MAX(0,MIN(22/24,B4+(B3>B4))-MAX(7/24,B3))

andyvande said:

any ideas or help, even if someone could modify the formula,
to count the hours from 14:00 - 23:00. I could adjust the rest of the sheet myself.

=MOD(B4-B3,1)-(B3>B4)*MEDIAN(0,B4-7/24,4.5/12)-MAX(0,MIN(22/24,B4+(B3>B4))-MAX(7/24,B3))

Click to expand...

I think you should try and copy a part of the excel sheet as it is now and post to the board. Later time minus early time seems very straight forward. I'm sure you must be looking for something more complicated so please post some data to the board, try using excel jeannie or similar

Group_Home_Payroll

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 68px"><COL style="WIDTH: 71px"><COL style="WIDTH: 68px"><COL style="WIDTH: 68px"><COL style="WIDTH: 69px"><COL style="WIDTH: 71px"><COL style="WIDTH: 73px"><COL style="WIDTH: 69px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">4/21/2009</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Sun</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Mon</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Tues</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Wed</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Thur</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Fri</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">Sat</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">20-Apr</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">21-Apr</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">22-Apr</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">23-Apr</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">24-Apr</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">25-Apr</TD><TD style="BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">26-Apr</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>START</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">3:00 PM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">8:00 AM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">6:00 AM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">7:00 AM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">8:00 AM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">9:00 AM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">10:00 AM</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>END</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">11:00 PM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">4:30 PM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">9:00 AM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">10:00 AM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">11:00 AM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">12:00 PM</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">1:00 PM</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Breaks</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">0.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">0.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">0.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">0.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">0.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">0.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">0.00 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Hours</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">8.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">8.50 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">3.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">3.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">3.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">3.00 </TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">3.00 </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Type</TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>14-23</TD><TD style="BACKGROUND-COLOR: #ccccff"> </TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">22-7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0.04 </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0.00 </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0.04 </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0.00 </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0.00 </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0.00 </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0.00 </TD><TD style="TEXT-ALIGN: right">2.00</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Weekend</TD><TD style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>O.T.</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD> </TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">7.00 </TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">8.50 </TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">2.00 </TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">3.00 </TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">3.00 </TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">3.00 </TD><TD style="BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: right">3.00 </TD><TD style="TEXT-ALIGN: right">29.5</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=A1-1</TD></TR><TR><TD>C2</TD><TD>=A1</TD></TR><TR><TD>D2</TD><TD>=C2+1</TD></TR><TR><TD>E2</TD><TD>=D2+1</TD></TR><TR><TD>F2</TD><TD>=E2+1</TD></TR><TR><TD>G2</TD><TD>=F2+1</TD></TR><TR><TD>H2</TD><TD>=G2+1</TD></TR><TR><TD>B6</TD><TD>=IF((OR(B4="",B3="")),0,IF((B4<B3),((B4-B3)*24)+24,(B4-B3)*24))-B5</TD></TR><TR><TD>C6</TD><TD>=IF((OR(C4="",C3="")),0,IF((C4<C3),((C4-C3)*24)+24,(C4-C3)*24))-C5</TD></TR><TR><TD>D6</TD><TD>=IF((OR(D4="",D3="")),0,IF((D4<D3),((D4-D3)*24)+24,(D4-D3)*24))-D5</TD></TR><TR><TD>E6</TD><TD>=IF((OR(E4="",E3="")),0,IF((E4<E3),((E4-E3)*24)+24,(E4-E3)*24))-E5</TD></TR><TR><TD>F6</TD><TD>=IF((OR(F4="",F3="")),0,IF((F4<F3),((F4-F3)*24)+24,(F4-F3)*24))-F5</TD></TR><TR><TD>G6</TD><TD>=IF((OR(G4="",G3="")),0,IF((G4<G3),((G4-G3)*24)+24,(G4-G3)*24))-G5</TD></TR><TR><TD>H6</TD><TD>=IF((OR(H4="",H3="")),0,IF((H4<H3),((H4-H3)*24)+24,(H4-H3)*24))-H5</TD></TR><TR><TD>C8</TD><TD>=IF(C7="E",C6,0)</TD></TR><TR><TD>D8</TD><TD>=IF(D7="E",D6,0)</TD></TR><TR><TD>E8</TD><TD>=IF(E7="E",E6,0)</TD></TR><TR><TD>F8</TD><TD>=IF(F7="E",F6,0)</TD></TR><TR><TD>G8</TD><TD>=IF(G7="E",G6,0)</TD></TR><TR><TD>H8</TD><TD>=IF(H7="E",H6,0)</TD></TR><TR><TD>I8</TD><TD>=SUM(C8:H8)</TD></TR><TR><TD>B9</TD><TD>=MOD(B4-B3,1)-(B3>B4)*MEDIAN(0,B4-7/24,4.5/12)-MAX(0,MIN(22/24,B4+(B3>B4))-MAX(7/24,B3))</TD></TR><TR><TD>C9</TD><TD>=MOD(C4-C3,1)-(C3>C4)*MEDIAN(0,C4-7/24,4.5/12)-MAX(0,MIN(22/24,C4+(C3>C4))-MAX(7/24,C3))</TD></TR><TR><TD>D9</TD><TD>=MOD(D4-D3,1)-(D3>D4)*MEDIAN(0,D4-7/24,4.5/12)-MAX(0,MIN(22/24,D4+(D3>D4))-MAX(7/24,D3))</TD></TR><TR><TD>E9</TD><TD>=MOD(E4-E3,1)-(E3>E4)*MEDIAN(0,E4-7/24,4.5/12)-MAX(0,MIN(22/24,E4+(E3>E4))-MAX(7/24,E3))</TD></TR><TR><TD>F9</TD><TD>=MOD(F4-F3,1)-(F3>F4)*MEDIAN(0,F4-7/24,4.5/12)-MAX(0,MIN(22/24,F4+(F3>F4))-MAX(7/24,F3))</TD></TR><TR><TD>G9</TD><TD>=MOD(G4-G3,1)-(G3>G4)*MEDIAN(0,G4-7/24,4.5/12)-MAX(0,MIN(22/24,G4+(G3>G4))-MAX(7/24,G3))</TD></TR><TR><TD>H9</TD><TD>=MOD(H4-H3,1)-(H3>H4)*MEDIAN(0,H4-7/24,4.5/12)-MAX(0,MIN(22/24,H4+(H3>H4))-MAX(7/24,H3))</TD></TR><TR><TD>I9</TD><TD>=SUM(B9:H9)*24</TD></TR><TR><TD>B10</TD><TD>=IF(B7="O",B6,0)</TD></TR><TR><TD>C10</TD><TD>=IF(C7="O",C6,0)</TD></TR><TR><TD>D10</TD><TD>=IF(D7="O",D6,0)</TD></TR><TR><TD>E10</TD><TD>=IF(E7="O",E6,0)</TD></TR><TR><TD>F10</TD><TD>=IF(F7="O",F6,0)</TD></TR><TR><TD>G10</TD><TD>=IF(G7="O",G6,0)</TD></TR><TR><TD>H10</TD><TD>=IF(H7="O",H6,0)</TD></TR><TR><TD>I10</TD><TD>=SUM(B10:H10)</TD></TR><TR><TD>B11</TD><TD>=IF(B6>12,B8-B9-B10,0)</TD></TR><TR><TD>C11</TD><TD>=IF(C6>12,C6-12,0)</TD></TR><TR><TD>D11</TD><TD>=IF(D6>12,D6-12,0)</TD></TR><TR><TD>E11</TD><TD>=IF(E6>12,E6-12,0)</TD></TR><TR><TD>F11</TD><TD>=IF(F6>12,F6-12,0)</TD></TR><TR><TD>G11</TD><TD>=IF(G6>12,G6-12,0)</TD></TR><TR><TD>H11</TD><TD>=IF(H6>12,H6-12,0)</TD></TR><TR><TD>I11</TD><TD>=SUM(B11:H11)</TD></TR><TR><TD>B12</TD><TD>=B6-(B8)-(B9*24)-(B10)-B11</TD></TR><TR><TD>C12</TD><TD>=C6-(C8*24)-(C9*24)-(C10*24)-C11</TD></TR><TR><TD>D12</TD><TD>=D6-(D8*24)-(D9*24)-(D10*24)-D11</TD></TR><TR><TD>E12</TD><TD>=E6-(E8*24)-(E9*24)-(E10*24)-E11</TD></TR><TR><TD>F12</TD><TD>=F6-(F8*24)-(F9*24)-(F10*24)-F11</TD></TR><TR><TD>G12</TD><TD>=G6-(G8*24)-(G9*24)-(G10*24)-G11</TD></TR><TR><TD>H12</TD><TD>=H6-(H8*24)-(H9*24)-(H10*24)-H11</TD></TR><TR><TD>I12</TD><TD>=SUM(B12:H12)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

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

i need something to count the 14-23 dont know why I'm having such trouble with it you can ignore the current if functions spannin c8-h8

b9-h9 counts from 11pm-7am

can ignore the rest of the formulas..

any ideas? Or am I out to lunch.

I'm at a loss as to what your trying to count with rows A8 and A9. I see what you have written in them, but sory, i can't fathom what your trying to do. Can you explain what the 0.4 is? how you arrived at it? can you say what your expecting to see in rows 8 or 9?