%age cross over

[spudgun44]

All,

I have a working week shift pattern for two people over 7 days

what I need is a fomula or something that will caculate the % crossover of the two people

Please help

 

[jbeaucaire]

Use Excel Jeanie to post up some visible examples of the data you're referring to, and perhaps a mockup of the desired results you have in mind.

That should help us see what you want and we can offer some ideas.

 

[spudgun44]

<table x:str="" style="border-collapse: collapse; width: 409pt;" border="0" cellpadding="0" cellspacing="0" width="544"><col style="width: 26pt;" width="35"> <col style="width: 48pt;" width="64"> <col style="width: 8pt;" width="11"> <col style="width: 29pt;" width="39"> <col style="width: 25pt;" width="33"> <col style="width: 44pt;" width="58"> <col style="width: 28pt;" width="37"> <col style="width: 29pt;" width="39"> <col style="width: 8pt;" width="11"> <col style="width: 29pt;" width="39"> <col style="width: 25pt;" width="33"> <col style="width: 44pt;" width="58"> <col style="width: 28pt;" width="37"> <col style="width: 38pt;" width="50"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 26pt;" width="35" height="17"> </td> <td class="xl24" style="width: 48pt;" width="64"> </td> <td class="xl24" style="width: 8pt;" width="11"> </td> <td class="xl24" style="width: 29pt;" width="39"> </td> <td class="xl24" style="width: 25pt;" width="33"> </td> <td class="xl24" style="width: 44pt;" width="58"> </td> <td class="xl24" style="width: 28pt;" width="37"> </td> <td class="xl24" style="width: 29pt;" width="39"> </td> <td class="xl24" style="width: 8pt;" width="11"> </td> <td class="xl24" style="width: 29pt;" width="39"> </td> <td class="xl24" style="width: 25pt;" width="33"> </td> <td class="xl24" style="width: 44pt;" width="58"> </td> <td class="xl24" style="width: 28pt;" width="37"> </td> <td class="xl24" style="width: 38pt;" width="50"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt;" height="17"> </td> <td colspan="4" rowspan="2" class="xl39" style="border-right: 0.5pt solid black; border-bottom: 0.5pt solid black; width: 110pt;" width="147">Person 1</td> <td class="xl37" style="border-left: medium none;"> </td> <td class="xl34"> </td> <td colspan="4" rowspan="2" class="xl39" style="border-right: 0.5pt solid black; border-bottom: 0.5pt solid black; width: 91pt;" width="122">Person 2</td> <td class="xl24"> </td> <td class="xl24"> </td> <td class="xl24"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl34" style="height: 12.75pt;" height="17"> </td> <td class="xl36" style="border-left: medium none;"> </td> <td class="xl35"> </td> <td class="xl38"> </td> <td class="xl38"> </td> <td class="xl38"> </td> </tr> <tr style="height: 33.75pt;" height="45"> <td class="xl35" style="height: 33.75pt;" height="45"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Start</td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Finish</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 25pt;" width="33">Meal
(Y/N)</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 44pt;" width="58">total hours</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 28pt;" width="37">Minus Lunch</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Start</td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Finish</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 25pt;" width="33">Meal
(Y/N)</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 44pt;" width="58">total hours</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 28pt;" width="37">Minus Lunch</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 38pt;" width="50">%age TL hours Xover</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">sun</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.29166666666666669" align="right">07:00</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:fmla="=IF(B5>0,"-","")">-</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.60416666666666663" align="right">14:30</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(D5-B5)*24">7.5</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(E5="Y",((D5-B5)*24)-0.5,(D5-B5)*24)" align="right">7</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.29166666666666669" align="right">07:00</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:fmla="=IF(H5>0,"-","")">-</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.6875" align="right">16:30</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(J5-H5)*24">9.5</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(K5="Y",((J5-H5)*24)-0.5,(J5-H5)*24)" align="right">9</td> <td class="xl32" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">mon</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.29166666666666669" align="right">07:00</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:fmla="=IF(B6>0,"-","")">-</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.6875" align="right">16:30</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(D6-B6)*24">9.5</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(E6="Y",((D6-B6)*24)-0.5,(D6-B6)*24)" align="right">9</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.29166666666666669" align="right">07:00</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:fmla="=IF(H6>0,"-","")">-</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.6875" align="right">16:30</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(J6-H6)*24">9.5</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(K6="Y",((J6-H6)*24)-0.5,(J6-H6)*24)" align="right">9</td> <td class="xl32" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">tue</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.33333333333333331" align="right">08:00</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:fmla="=IF(B7>0,"-","")">-</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.6875" align="right">16:30</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(D7-B7)*24">8.5</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(E7="Y",((D7-B7)*24)-0.5,(D7-B7)*24)" align="right">8</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.29166666666666669" align="right">07:00</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:fmla="=IF(H7>0,"-","")">-</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.6875" align="right">16:30</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(J7-H7)*24">9.5</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(K7="Y",((J7-H7)*24)-0.5,(J7-H7)*24)" align="right">9</td> <td class="xl32" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">wed</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.29166666666666669" align="right">07:00</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:fmla="=IF(B8>0,"-","")">-</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.72916666666666663" align="right">17:30</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(D8-B8)*24">10.5</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(E8="Y",((D8-B8)*24)-0.5,(D8-B8)*24)" align="right">10</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.29166666666666669" align="right">07:00</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:fmla="=IF(H8>0,"-","")">-</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.6875" align="right">16:30</td> <td class="xl30" style="border-top: medium none; border-left: medium none;">Y</td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(J8-H8)*24">9.5</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(K8="Y",((J8-H8)*24)-0.5,(J8-H8)*24)" align="right">9</td> <td class="xl32" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">thu</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str="" x:fmla="=IF(B9>0,"-","")"> </td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl30" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(D9-B9)*24">0</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(E9="Y",((D9-B9)*24)-0.5,(D9-B9)*24)" align="right">0</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str="" x:fmla="=IF(H9>0,"-","")"> </td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl30" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(J9-H9)*24">0</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(K9="Y",((J9-H9)*24)-0.5,(J9-H9)*24)" align="right">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">fri</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str="" x:fmla="=IF(B10>0,"-","")"> </td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl30" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(D10-B10)*24">0</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(E10="Y",((D10-B10)*24)-0.5,(D10-B10)*24)" align="right">0</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str="" x:fmla="=IF(H10>0,"-","")"> </td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl30" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(J10-H10)*24">0</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(K10="Y",((J10-H10)*24)-0.5,(J10-H10)*24)" align="right">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">sat</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str="" x:fmla="=IF(B11>0,"-","")"> </td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl30" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(D11-B11)*24">0</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(E11="Y",((D11-B11)*24)-0.5,(D11-B11)*24)" align="right">0</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:str="" x:fmla="=IF(H11>0,"-","")"> </td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl30" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl31" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=(J11-H11)*24">0</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="" x:fmla="=IF(K11="Y",((J11-H11)*24)-0.5,(J11-H11)*24)" align="right">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">Total</td> <td colspan="4" class="xl33" style="border-left: medium none;" x:num="" x:fmla="=SUM(G5:G11)">34</td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td colspan="4" class="xl33" style="border-left: medium none;" x:num="" x:fmla="=SUM(M5:M11)">36</td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl25" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>

 

[jbeaucaire]

As requested before, please provide your expected results in the colored cells below, and explain your math if it's not obvious. Then I'll be able to suggest a formula to follow your same logic:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		PERSON 1							PERSON 2
2		Start		Finish	Meal	total hours	Minus Lunch		Start		Finish	Meal	total hours	Minus Lunch	%age TL hours Xover
3	sun	7:00	-	14:30	Y	7.5	7		7:00	-	16:30	Y	9.5	9
4	mon	7:00	-	16:30	Y	9.5	9		7:00	-	16:30	Y	9.5	9
5	tue	8:00	-	16:30	Y	8.5	8		7:00	-	16:30	Y	9.5	9
6	wed	7:00	-	17:30	Y	10.5	10		7:00	-	16:30	Y	9.5	9
7	thu					0	0						0	0
8	fri					0	0						0	0
9	sat					0	0						0	0
10	Total						34							36
Sheet2

 

[spudgun44]

As requested before, please provide your expected results in the colored cells below, and explain your math if it's not obvious. Then I'll be able to suggest a formula to follow your same logic:
Sheet2

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 42px;"><col style="width: 39px;"><col style="width: 17px;"><col style="width: 47px;"><col style="width: 37px;"><col style="width: 51px;"><col style="width: 55px;"><col style="width: 33px;"><col style="width: 39px;"><col style="width: 15px;"><col style="width: 46px;"><col style="width: 35px;"><col style="width: 55px;"><col style="width: 65px;"><col style="width: 87px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td style="border: 3px outset rgb(240, 240, 240);">A</td><td style="border: 3px outset rgb(240, 240, 240);">B</td><td style="border: 3px outset rgb(240, 240, 240);">C</td><td style="border: 3px outset rgb(240, 240, 240);">D</td><td style="border: 3px outset rgb(240, 240, 240);">E</td><td style="border: 3px outset rgb(240, 240, 240);">F</td><td style="border: 3px outset rgb(240, 240, 240);">G</td><td style="border: 3px outset rgb(240, 240, 240);">H</td><td style="border: 3px outset rgb(240, 240, 240);">I</td><td style="border: 3px outset rgb(240, 240, 240);">J</td><td style="border: 3px outset rgb(240, 240, 240);">K</td><td style="border: 3px outset rgb(240, 240, 240);">L</td><td style="border: 3px outset rgb(240, 240, 240);">M</td><td style="border: 3px outset rgb(240, 240, 240);">N</td><td style="border: 3px outset rgb(240, 240, 240);">O</td></tr><tr style="height: 17px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">1</td><td> </td><td colspan="6" style="font-weight: bold; text-align: center;">PERSON 1</td><td> </td><td colspan="6" style="font-weight: bold; text-align: center;">PERSON 2</td><td> </td></tr><tr style="height: 35px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">Start </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold;"> </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">Finish </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">Meal</td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">total hours </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">Minus Lunch </td><td style="font-weight: bold;"> </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">Start </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold;"> </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">Finish </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">Meal</td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">total hours </td><td style="border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: center;">Minus Lunch </td><td style="font-weight: bold; text-align: center;">%age TL hours Xover</td></tr><tr style="height: 17px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="border-left: 2px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); font-weight: bold; text-align: right;">sun </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7:00</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">-</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">14:30</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Y</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7.5</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7</td><td style="border-right: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7:00</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">-</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">16:30</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Y</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9.5</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9</td><td style="background-color: rgb(204, 255, 255);"> 100%</td></tr><tr style="height: 17px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="border-left: 2px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); font-weight: bold; text-align: right;">mon </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7:00</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">-</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">16:30</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Y</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9.5</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9</td><td style="border-right: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7:00</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">-</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">16:30</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Y</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9.5</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9</td><td style="background-color: rgb(204, 255, 255);"> 100%</td></tr><tr style="height: 17px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="border-left: 2px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); font-weight: bold; text-align: right;">tue </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">8:00</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">-</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">16:30</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Y</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">8.5</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">8</td><td style="border-right: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7:00</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">-</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">16:30</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Y</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9.5</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9</td><td style="background-color: rgb(204, 255, 255);"> 90%</td></tr><tr style="height: 17px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="border-left: 2px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); font-weight: bold; text-align: right;">wed </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7:00</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">-</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">17:30</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Y</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">10.5</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">10</td><td style="border-right: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">7:00</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">-</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">16:30</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">Y</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9.5</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">9</td><td style="background-color: rgb(204, 255, 255);"> 100%</td></tr><tr style="height: 17px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="border-left: 2px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); font-weight: bold; text-align: right;">thu </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">0</td><td> </td></tr><tr style="height: 17px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="border-left: 2px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); font-weight: bold; text-align: right;">fri </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); text-align: center;">0</td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="border-left: 2px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0); font-weight: bold; text-align: right;">sat </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0);"> </td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0); text-align: center;">0</td><td style="border-right: 2px solid rgb(0, 0, 0); border-bottom: 2px solid rgb(0, 0, 0); text-align: center;">0</td><td> </td></tr><tr style="height: 17px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="font-weight: bold;">Total </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: center;">34</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td style="text-align: center;">36</td><td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>F3</td><td>=(D3-B3)*24</td></tr><tr><td>G3</td><td>=F3-(0.5*(E3="Y"))</td></tr></tbody></table></td></tr></tbody></table>

I need to know if it is possible to get a %age cross over figure per day for a team member and their team leader in a cell then a total at the bottom over the week.

I naively tried working out the hours in the shift and comparing them, but this of course would show someone on a 07:00- 13:00 having a 100% crossover with 17:00-23:00 (as both 6 hours)

I need it to show that if a person is on a 07:00 - 16:30 shift and their teem leader is on a 13:30 - 23:00 this is a 3 hour cross over and there for 33% of the team leaders hours on that day. This in tun I can then do for each day with a total at the bottom


Thanks for all your help on this

 

[wsjackman]

maybe

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		PERSON 1							PERSON 2
2		Start		Finish	Meal	total hours	Minus Lunch		Start		Finish	Meal	total hours	Minus Lunch	%age TL hours Xover
3	sun	7:00	-	14:30	Y	7.5	7		7:00	-	16:30	Y	9.5	9	100%
4	mon	7:00	-	16:30	Y	9.5	9		7:00	-	16:30	Y	9.5	9	100%
5	tue	8:00	-	16:30	Y	8.5	8		7:00	-	16:30	Y	9.5	9	100%
6	wed	7:00	-	17:30	Y	10.5	10		7:00	-	16:30	Y	9.5	9	90%
7	thu					0	0						0	0
8	fri					0	0						0	0
9	sat					0	0						0	0
Sheet11

Excel Workbook
	O
3	100%
Sheet11

 

[spudgun44]

wow, I cant believe the speed on these reply to me

Thank you to all that have helped so far

wsjackman thank you, the formula is what I was looking for. all i need to do now is to nest it in an if statement so if person 1 or peson 2 is not on shift on the same day then 0% displayed and add a total week %age at the bottom that i think should ignore the 0 so a true week crossover %age is caculated

that is unless anyone out there knows how to do this!!??

 

[wsjackman]

maybe

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		PERSON 1							PERSON 2
2		Start		Finish	Meal	total hours	Minus Lunch		Start		Finish	Meal	total hours	Minus Lunch	%age TL hours Xover
3	sun	7:00	-	14:30	Y	7.5	7		7:00	-	16:30	Y	9.5	9	100%
4	mon	7:00	-	16:30	Y	9.5	9		7:00	-	16:30	Y	9.5	9	100%
5	tue	8:00	-	16:30	Y	8.5	8		7:00	-	16:30	Y	9.5	9	100%
6	wed	7:00	-	17:30	Y	10.5	10		7:00	-	16:30	Y	9.5	9	90%
7	thu					0	0						0	0	0%
8	fri					0	0						0	0	0%
9	sat					0	0						0	0	0%
10															97.22%
Sheet11

 

[wsjackman]

wow, I cant believe the speed on these reply to me

Thank you to all that have helped so far

wsjackman thank you, the formula is what I was looking for. all i need to do now is to nest it in an if statement so if person 1 or peson 2 is not on shift on the same day then 0% displayed and add a total week %age at the bottom that i think should ignore the 0 so a true week crossover %age is caculated

that is unless anyone out there knows how to do this!!??

I don't know what you mean by "ignore the 0"

 

[spudgun44]

Ignore the 0 was me thinking out loud,I know there is 7 days so didn't want the total %age be distorted by the 0, but with the formula you have kindly provided it sorts my problem

thanks again for your help, I am learning things fast!!