# Hours, rates and percentages

#### strooman

##### Active Member
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)));"")

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
The 1st part of E2 and G2 can be reduced from:
Code:
``=IF(AND(WEEKDAY(B2;2)>=1;WEEKDAY(B2;2)<=5);....``
to:
Code:
``=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:
Code:
``=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.

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

Replies
1
Views
199
Replies
7
Views
322
Replies
4
Views
343
Replies
3
Views
314
Replies
0
Views
218

1,196,098
Messages
6,013,437
Members
441,766
Latest member
ixruiz

### 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?

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