# Formula to conscise

##### Well-known Member
hi there
I have a formula that works that calculates overtime rates depending on what variables you enter for time/half or double time to start.

eg would be an 8 hour working day with time?half from 8-10 hour then double time after that.

=SUM(\$AZ10*BB10)+(IF(INT(BA10)+(BA10-INT(BA10))/0.6<\$E\$72,0,IF(\$E\$72<\$E\$74,MAX(0,(INT(BA10)+(BA10-INT(BA10))/0.6-\$E\$72-(\$E\$74-\$E\$72)))*2+(IF((BA10-\$E\$72)<(\$E\$74-\$E\$72),(INT(BA10)+(BA10-INT(BA10))/0.6-\$E\$72),(\$E\$74-\$E\$72)))*1.5,(INT(BA10)+(BA10-INT(BA10))/0.6-\$E\$72)*2))*\$FY10)*BB10

AZ10 = daily rate - user entered
BB10 = no of days - user entered
BA10 = No of hours in a day - user entered
E72 = base daily rate (after time/half starts)
E74 = double time starts
FY10 = hourly rate (daily rate/ base hourly day ie 8hr)

does anyone know of a way of condensing/simplifying this formula?

Johnny

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### GlennUK

##### Well-known Member
Please give the logic that you are aiming for in English. Trying to decipher someone's formula and infer the intention is not so easy, especially when there may be errors involved.

##### Well-known Member
Thanks glen,

yes the current formula works fine, it is essentially a wage counter where the user enters the daily wage amount, number of worker hours each day and the total number of days.

from there the formula calculates the total value based on the overtime structures, ie if some one was hired for a base day of 8 hours and they worked 12, then hours 8-10 would be calculated on x1.5 and any hour after that would be on x2 time. (please refer to my cell reference as to which cell are what).

hope that clears it up and thanks for your help.

johnny

#### barry houdini

##### MrExcel MVP
I'm not sure I understand what's in all the cells

Can you give an example of the entry in each of the cells you've specified?

When dealing with time it's useful to know the format in which you are entering data, e.g. is the number of hours in a day 8.5 or 8:30? It makes a big difference to how the formula works

##### Well-known Member

Its base 6 so you would write hallf past 8 as 8.30 or 8 and a half hours

AZ10 = daily rate - user entered (eg\$500 a day)
BB10 = no of days - user entered (eg 3 days)
BA10 = No of hours in a day worked - user entered (12 hour day)
E72 = base daily rate (after time/half starts) minimum hours before overtime is calculated eg 8 hour day)
E74 = double time starts (eg 10 hours so that the difference between E74 & E72 is calculated at x1.5 rate
FY10 = hourly rate (daily rate/ base hourly day ie 8hr)

So that an 12 hour day would calculate 2 hours at time and a hald and 2 hours at doble time = the daily rate and then times all of t by the number of days.

hope that clarifies it a little more.

thanks barry if you can make sense

#### barry houdini

##### MrExcel MVP
Do you have to input times in that format, that's one of the things that makes your formula so complex?

If you input times in time format, i.e. with a colon like 8:30 then you should be able to use

=(E72+MEDIAN(E74-E72,0,BA10-E72)*1.5+MAX(0,BA10-E74)*2)*BB10*FY10*24

##### Well-known Member

I think I have over confused the issue.

=(daily rate(eg500) + Overtime(eg12hrs) (hours>greater than base of 8 hours) where by hours >8<10@ x1.5 + hours>10 @ x2) * No days

but I need the equation to be that no matter what user value is substituted for
base hours, x1.5 & x2 ie 8hr for base & >x1.5, & >10hr for x2 the equation can produce a value.

hope that makes sense, if it in time of 8.30 or 8.5 i can go either way.

I've made the formula so that you can work on my example
Code:
``=SUM(G2*F2)+(IF(INT(E2)+(E2-INT(E2))/0.6<B2,0,IF(B2<C2,MAX(0,(INT(E2)+(E2-INT(E2))/0.6-B2-(C2-B2)))*2+(IF((E2-B2)<(C2-B2),(INT(E2)+(E2-INT(E2))/0.6-B2),(C2-B2)))*1.5,(INT(E2)+(E2-INT(E2))/0.6-B2)*2))*A2)*F2``

A2 - hourly rate
B2 - base hours
C2 - double time starts after

E2 - Hours worked - user entered
F2 - no.of days - user entered
G2 - Daily rate - user entered

H2 - total

PS if its easy to factor in a triple time factor that would be a plus?

cheers barry

#### barry houdini

##### MrExcel MVP
Assuming you input all hours in decimal format, e.g. 8.5 or 8.75 then you could use

=(G2+(MAX(0,E2-C2)*2+MEDIAN(0,E2-B2,C2-B2)*1.5)*A2)*F2

If you have a triple time threshold in D2 then you can revise that as follows:

=(G2+(MAX(0,E2-D2)*3+MEDIAN(0,E2-C2,D2-C2)*2+MEDIAN(0,E2-B2,C2-B2)*1.5)*A2)*F2

.....or for an entirely different appoach - this will give you the same result as formula 2

=(SUMPRODUCT(--(E2>B2:D2),E2-B2:D2,{1.5,0.5,1})*A2+G2)*F2

##### Well-known Member
You are a genius!

Thanks for that, sorry I took you on the long way to get it.

I can now put this into my other argument without it being so complicated.

johnny

Just on a side note is there a way so that if triple time is blank, it does not affect the equation.

It seems on my version that if I leave triple blank it almost doubles the out come? - any suggestion, but understand if your over it!

##### Well-known Member
Slight amendment from my last log:

This is the formula that I am working with.

Code:
`` =(G2+(MAX(0,E2-D2)*3+MEDIAN(0,E2-C2,D2-C2)*2+MEDIAN(0,E2-B2,C2-B2)*1.5)*A2)*F3``

I was just wondering how I change it so that the Hours E2 works on a base 6 ie so that quater past six is 6.15 and that the formula can adjust accordingly?

Any suggestions

Replies
12
Views
426
Replies
1
Views
286
Replies
5
Views
198
Replies
1
Views
260
Replies
0
Views
672

### Forum statistics

1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc ### 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.

### Which adblocker are you using?    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

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