Hours, rates and percentages

[strooman]

This is about working shifts and inconvenient rates. Formulas goes in E2, F2, G2, H2, I2 and copied down. The rates table is in columns K, L, M, N. I have formulas which work fine to calculate the amount of hours that fall in particular slots.
So I'm just curious if the formulas can be simplified.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	day	date	start	stop	122%	138%	144%	149%	160%		Day	Percentage	Start	Stop
2	Monday	18-05-15	15:00	23:30	2,00		1,50				12345	144%	0:00	6:00
3	Thuesday	19-05-15	6:00	11:00	1,00						12345	122%	6:00	7:00
4	Wednesday	20-05-15	14:00	22:30	2,00		0,50				12345	122%	20:00	22:00
5	Thursday	21-05-15	18:00	23:00	2,00		1,00				12345	144%	22:00	24:00
6	Friday	22-05-15	6:00	11:00	1,00						6	149%	0:00	6:00
7	Saturday	23-05-15	12:00	23:30		10,00		1,50			6	138%	6:00	8:00
8	Sunday	24-05-15	7:00	15:00					8,00		6	138%	12:00	22:00
9											6	149%	22:00	24:00
10											7	160%	0:00	24:00

<tbody>
</tbody>

Formulas:

[E2]=IF(AND(WEEKDAY(B2;2)>=1;WEEKDAY(B2;2)<=5);IF(SUM(MAX(($N$3-$M$3-MAX(0;C2-$M$3)-MAX(0;$N$3-D2))*24;0);MAX(($N$4-$M$4-MAX(0;C2-$M$4)-MAX(0;$N$4-D2))*24;0))=0;"";SUM(MAX(($N$3-$M$3-MAX(0;C2-$M$3)-MAX(0;$N$3-D2))*24;0);MAX(($N$4-$M$4-MAX(0;C2-$M$4)-MAX(0;$N$4-D2))*24;0)));"")

[F2]=IF(WEEKDAY(B2;2)=6;IF(SUM(MAX(($N$7-$M$7-MAX(0;C2-$M$7)-MAX(0;$N$7-D2))*24;0);MAX(($N$8-$M$8-MAX(0;C2-$M$8)-MAX(0;$N$8-D2))*24;0))=0;"";SUM(MAX(($N$7-$M$7-MAX(0;C2-$M$7)-MAX(0;$N$7-D2))*24;0);MAX(($N$8-$M$8-MAX(0;C2-$M$8)-MAX(0;$N$8-D2))*24;0)));"")

[G2]=IF(AND(WEEKDAY(B2;2)>=1;WEEKDAY(B2;2)<=5);IF(SUM(MAX(($N$2-$M$2-MAX(0;C2-$M$2)-MAX(0;$N$2-D2))*24;0);MAX(($N$5-$M$5-MAX(0;C2-$M$5)-MAX(0;$N$5-D2))*24;0))=0;"";SUM(MAX(($N$2-$M$2-MAX(0;C2-$M$2)-MAX(0;$N$2-D2))*24;0);MAX(($N$5-$M$5-MAX(0;C2-$M$5)-MAX(0;$N$5-D2))*24;0)));"")

[H2]=IF(WEEKDAY(B2;2)=6;IF(SUM(MAX(($N$6-$M$6-MAX(0;C2-$M$6)-MAX(0;$N$6-D2))*24;0);MAX(($N$9-$M$9-MAX(0;C2-$M$9)-MAX(0;$N$9-D2))*24;0))=0;"";SUM(MAX(($N$6-$M$6-MAX(0;C2-$M$6)-MAX(0;$N$6-D2))*24;0);MAX(($N$9-$M$9-MAX(0;C2-$M$9)-MAX(0;$N$9-D2))*24;0)));"")

[I2]=IF(OR(WEEKDAY(B2;2)=7;Q11=1);IF(SUM(MAX(($N$10-$M$10-MAX(0;C2-$M$10)-MAX(0;$N$10-D2))*24;0))=0;"";SUM(MAX(($N$10-$M$10-MAX(0;C2-$M$10)-MAX(0;$N$10-D2))*24;0)));"")

 

[RonB1111]

The 1st part of E2 and G2 can be reduced from:

=IF(AND(WEEKDAY(B2;2)>=1;WEEKDAY(B2;2)<=5);....

to:

=IF(WEEKDAY(B2;2)<=5;......

If it's ok for Cols E thru I to contain a zero value but just not display it, you can format the cells to hide 0's either via Conditional Formatting where you make the font color white if the cell = 0, or hide the 0's thru FormatCells-Custom with this custom format: 0;-0;;@
Then E2 can be:

=IF(WEEKDAY(B2;2)<=5;SUM(MAX(($N$3-$M$3-MAX(0;C2-$M$3)-MAX(0;$N$3-D2))*24;0);MAX(($N$4-$M$4-MAX(0;C2-$M$4)-MAX(0;$N$4-D2))*24;0));"")

and similar shortening for the other formulas.

 

[strooman]

Thanks Ron, These suggestion come in very handy. It makes the formulas more readable.