IF formula

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
I am doing timesheets and the following is the layout

E F G H I J
Start Break Finish Normal Hours Time & Half Double Time
08:30 1:00 19:30 8 3 2

Under the H cell I use this formula =IF(((G5-F5-E5)*24)>8,8,(G5-F5-E5)*24) to calculate the hours and to stop them at 8

Under the I cell I have this formula =IF(((G5-F5-E5)*24)>8,((((G5-F5-E5)*24)-8)*1.5)) to calculate the remainder of the hours at time and a half (X1.5)

Under the J Cell I have this formula =IF(((G5-F5-E5)*24)>8,((((G5-F5-E5)*24)-8)*1.5)>2)*2 to calculate the remainder as double time

What I need is the I cell (time and a half) to work similar to the H cell so I need it to take the remainder of the hours worked which in this example is 2 hours multiply it by 1.5 (which works in the formula I have used) now I need it to stop at 2 and then I need the rest of the hours which would be 1 to multiply in the J cell to double time (x2)

Can anyone help please :eek:

TIA
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

petertenthije

Active Member
Joined
Sep 25, 2012
Messages
257
Re: IF formula help

Do I understand you correctly?

You want all hours worked up to 8 hours to be counted as 1.
All hours worked between 8 and 10 counted x1.5. So up to two hours can be counted x1.5
All hours worked over 10 counted x2.

If so:

Column I (x1.5).
The number 2 marked red can be changed to increase/decrease the amount of hours for which x1.5 applies.
=IF(((G5-F5-E5)*24)>8,(MIN((((G5-F5-E5)*24)-8),2)*1.5))

Column J (x2.0).
The number 10 marked red can be changed to modify the hour at which x2.0 starts.
=IF(((G5-F5-E5)*24)>10,((G5-F5-E5)*24)-10)*2
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
Re: IF formula help

Hi Nunya1,

I've just answered an identical question for Shazzi1005 but without the break hour(s).
https://www.mrexcel.com/forum/excel-questions/1108760-formula-excel-post5336244.html#post5336244

The same answer applies but with the break subtracted, as you've identified. I've added a total hours worked column for clarity.


EFGHIJKL
3Start TimeBreakEnd TimeCap at 8 8 to 10 x 1.5Over10 x 2Hours Worked
49:001:0018:008008
58:301:0019:3083010
67:001:0021:0083613
710:002:0016:004004

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Nunya1

Worksheet Formulas
CellFormula
H4=IF(((G4-F4-E4)*24)>8,8,(G4-F4-E4)*24)
I4=IF(((G4-F4-E4)*24) > 8,MIN(((G4-F4-E4)*24),10)-8)*1.5
J4=IF(((G4-F4-E4)*24) > 10,((((G4-F4-E4)*24)-10)*2),0)
L4
=((G4-F4-E4)*24)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Re: IF formula help

Ta I just flicked over to the other post and it answered my question. :)
 

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31

ADVERTISEMENT

Re: IF formula help

One more thing - So below is what our timesheets look like. Now the guys can do more then 1 job a day so if for the first job (row 4) they work the 8 hours then the second line (Row 5) they worked 4 hours then that row should go into the overtime hours so how can I get it to recognise that if rows 4-6 total 8 all together under normal then the first 1.33 hours is time and a half and the rest of the hours go in at double time. Each one is rows 4-6 and also why is my formula showing false until I enter data in times?

I am using the formulas written in this thread except in time and half I am using this formula =IF(((G4-F4-E4)*24)>8,(MIN((((G4-F4-E4)*24)-8),1.333333)*1.5))






<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
DaySite NameJob NoDateStart TimeBreakFinish TimeNormal 1.5 Time2.0 TimeOn CallTOIL TakenPublic HolidaySick LeaveAnnual Leave$70.00$130.00$195.00
Monday1-Jan-1907:00am09:00pm8.002.008.00
09:00am12:00pm3.00FALSE0.00
12:00pm01:0004:00pm3.00FALSE0.00
Tuesday
Wednesday
Thursday

<colgroup><col style="width: 132px"><col width="364"><col width="197"><col width="145"><col width="113"><col width="113"><col width="113"><col width="113"><col width="128"><col width="116"><col width="146"><col width="112"><col width="106"><col width="98"><col width="97"><col width="83"><col width="71"><col width="72"><col width="63"><col width="63"><col width="63"><col width="63"><col width="63"><col width="63"><col width="63"><col width="63"><col width="55"><col width="55"><col width="55"></colgroup><tbody>
</tbody>
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
Re: IF formula help

Hi Nunya1,

So this needs a different approach and a change to the structure of the sheet.

The Normal, OT1 and OT2 hours can only be calculated after the last entry for that day, so I need the date repeated for each row with times and I need a new column "Hours" so I can total up the hours for a day and then calculate.

Cell formulae H2, I2, J2 and K2 should be copied down as far as the last row which may have times entered.

The formulae search the whole column (i.e. H:H and D:D) but if you've other data in lower rows then you should limit the range to those which have times entered (e.g. $H$2:$H$100 and $D$2:$D$100).

I am showing the hours for OT1 and OT2. If you want me to actually calculate those hours as multiplied by 1.5 and 2 then let me know.


ABCDEFGHIJK
1DaySite NameJob NoDateStart TimeBreakFinish TimeHoursNormalOT 1OT 2
2Monday01-Jan-197:0021:0014.00
301-Jan-199:0012:003.00
401-Jan-1912:001:0016:003.008.001.3310.67
5Tuesday02-Jan-198:301:0017:007.50
602-Jan-197:301:0016:308.008.001.336.17
7
8Wednesday03-Jan-198:001:0016:307.507.50
9
10Thurday04-Jan-199:001:0017:007.007.00

<tbody>
</tbody>
Nunya1 (2)

Worksheet Formulas
CellFormula
H2=IF(E2<>"",(G2-E2-F2)*24,"")
I2=IF(AND(D2<>D3,D2<>""),MIN(SUMIFS(H:H,D:D,D2),8),"")
J2=IF(AND(ISNUMBER(I2),SUMIFS(H:H,D:D,D2)>8),MIN(SUMIFS(H:H,D:D,D2)-8,1.33333),"")
K2=IF(ISNUMBER(J2),SUMIFS(H:H,D:D,D2)-9.333333,"")

<tbody>
</tbody>

<tbody>
</tbody>
 

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31

ADVERTISEMENT

Re: IF formula help

And how do I get the J2 to multiply by 1.5 to get a total value of 2 and the same with K2 I need it to multiply by 2 to get a total?

The formulas you gave me work appreciated that but when I try to *1.5 or *2 it just brings it up as (Value)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
Re: IF formula help

Here you go...

ABCDEFGHIJK
1DaySite NameJob NoDateStart TimeBreakFinish TimeHoursNormalOT 1OT 2
2Monday01-Jan-197:0021:0014.00
301-Jan-199:0012:003.00
401-Jan-1912:001:0016:003.008.002.0021.33
5Tuesday02-Jan-198:301:0017:007.50
602-Jan-197:301:0016:308.008.002.0012.33
7
8Wednesday03-Jan-198:001:0016:307.507.50
9
10Thurday04-Jan-199:001:0017:007.007.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Nunya1 (3)

Worksheet Formulas
CellFormula
H2=IF(E2<>"",(G2-E2-F2)*24,"")
I2=IF(AND(D2<>D3,D2<>""),MIN(SUMIFS(H:H,D:D,D2),8),"")
J2=IF(AND(ISNUMBER(I2),SUMIFS(H:H,D:D,D2)>8),MIN(SUMIFS(H:H,D:D,D2)-8,1.33333)*1.5,"")
K2
=IF(ISNUMBER(J2),(SUMIFS(H:H,D:D,D2)-9.333333)*2,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Re: IF formula help

K still wont calculate at double time with that formula the J one works but K won'?

Appreciate your help BTW
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,464
Office Version
  1. 2016
Platform
  1. Windows
Re: IF formula help

It's working for me (as per the example).

1st January has three entries for 14,3 and 3 hours so total = 20.
Normal is 8 hours at flat rate x 1 = 8, so that leaves 12 hours to pay.
OT 1 is capped at 1.33333 hours and paid at 1.5 x so 1.5 x 1.33333 = 1.99999995 which Excel rounds to 2,so that leaves 20-8-1.33333 hours=10.66667
OT 2 is twice the remaining hours so 10.66667 x 2 = 21.33334 which Excel rounds to 21.33

2nd January had two entries 7.50 + 8 = 15.5 hours
Normal is 8 hours so leaving 7.5 hours
OT 1 takes 1.33333 of those 7.5 hours and x 2=2, so leaving 6.16667 hours
OT 2 takes the remaining hours and applies double rate so 6.16667 x 2 = 12.33334 which Excel rounds to 12.33

If those aren't the results you're seeing then please copy and paste the cells with your results?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,149
Messages
5,640,400
Members
417,140
Latest member
whiteprose

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
Top