Hours, rates and percentages

strooman

Active Member
Joined
Oct 29, 2013
Messages
314
Office Version
  1. 2016
Platform
  1. Windows
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
LM
N
1day
datestartstop122%138%144%149%160%
DayPercentageStartStop
2Monday18-05-1515:0023:302,00
1,5012345144%0:006:00
3Thuesday19-05-156:0011:001,00
12345122%6:007:00
4Wednesday20-05-1514:0022:302,00
0,5012345122%20:0022:00
5Thursday21-05-1518:0023:002,00
1,0012345144%22:0024:00
6Friday22-05-156:0011:001,006149%0:006:00
7Saturday23-05-1512:0023:3010,00
1,506138%6:008:00
8Sunday24-05-157:0015:008,006138%12:0022:00
96149%22:0024:00
107160%0:0024: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.
 
Upvote 0
Thanks Ron, These suggestion come in very handy. It makes the formulas more readable.
 
Upvote 0

Forum statistics

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

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
Back
Top