# IF formula

#### Nunya1

##### New Member
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)

TIA

#### Nunya1

##### New Member
Re: IF formula help

I did what you said and it is still not calculating correctly it is returning 18.67 instead of 21.33

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Nunya1

##### New Member
Re: IF formula help

Tried what you said it is not calculating correctly it is returning a value of 18.67

I have altered it a bit though as I am trying to make row 5 as the total row and have it calculate in there so it would read cells I5 and J5. On saying this I tried to just do it your way and it still wouldn't calculate

=IF(ISNUMBER(J5),(SUMIFS(H:H,D:D,D2)-9.333333)*2,"")*2 - This is how I have it in as when I don't do the *2 at the end it just returns 9.333 and wont multiply at all

#### Nunya1

##### New Member
Re: IF formula help

Ok So I have been playing with it and I have got this formula

=IF(ISNUMBER(J5),(SUMIFS(H:H,D:D,D5)-10.67)*2,"") - This returns -21.34 I am so close i can taste it haha but why is it bringing me up a negative

##### Well-known Member
Re: IF formula help

Please go back to the formulae I sent so we can figure out where the problem lies. For the last row with a 1st January date please paste here the formulae in cells I, J and K so I can figure out why you get 18.67.

That OT 2 calculation is =IF(ISNUMBER(J4),(SUMIFS(H:H,D:D,D4)-9.333333)*2,"")
because the Normal time takes the first 8 hours, the OT 1 time takes 1.33333 hours so if there is any left over then we take the total and subtract that 8+1.33333 hours or 9.33333 hours; it can't be a different number.

#### Nunya1

##### New Member

Re: IF formula help

 Day Site Name Job No Date Start Time Break Finish Time Hours Normal 1.5 Time 2.0 Time Monday 01-Jan-19 7:00 21:00 14.00 01-Jan-19 9:00 12:00 3.00 01-Jan-19 12:00 1:00 16:00 3.00 Under normal alongside the last row it is this =IF(AND(D2<>D3,D2<>""),MIN(SUMIFS(H:H,D:D,D2),8),"")+ Under 1.5 it is =IF(AND(ISNUMBER(I2),SUMIFS(H:H,D:D,D2)>8),MIN(SUMIFS(H:H,D:D,D2)-8,1.33333)*1.5,"") Under double it is =IF(ISNUMBER(J2),(SUMIFS(H:H,D:D,D2)-9.333333)*2,"") By chance is the I2 supposed to read as H2? When I re did it exactly as yours now it is not coming up with anything. I have checked the format of the sheet

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

##### Well-known Member
Re: IF formula help

I see the problem. The last row is probably row 4 but you've inserted the formulae which need to start at row 2 (which is why it uses D2 and only looks at D3 to see if the next entry is the same day).

Put the formulae into the associated cells (H2, I2, J2 and K2) then select all four cells and Copy. Now position your cursor at H3 and drag it down as far as you'll ever need, then Paste.

ABCDEFGHIJK
1DaySite NameJob NoDateStart TimeBreakFinish TimeHoursNormal1.5 Time2.0 Time
2Monday01-Jan-197:0021:0014
301-Jan-199:0012:003
401-Jan-1912:001:0016:00382.0021.33

</tbody>
Nunya5

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,"")

</tbody>

<tbody>
</tbody>

#### Nunya1

##### New Member

Re: IF formula help

Thank you that has worked One last question to finish it off, How do I get a cell to record at 4 hour time so for start and finish I would like them in 24 hour format as these guys will just type 2:00 and not give me AM or PM?

##### Well-known Member
Re: IF formula help

You're welcome!

I'm not aware of a way of restricting the format they enter time without resorting to VBA.

I'd suggest two things:
1. In the headings for time add "24hr clock" so Start Time would become "Start Time (24hr clock)" as a reminder.
2. Format all time cells with Format Cells, Category of Time and Type of 1:30 PM so that an entry of 02:00 would appear as "02:00 AM" which should prompt them to re-enter as 14:00.

One last thing. If you're having others enter the times into the sheet then I strongly recommend the cells with formulae be Locked then Protect the sheet, otherwise you know somebody will just overtype the formulae.

#### Nunya1

##### New Member
Re: IF formula help

Really appreciate all your help with this.

#### Nunya1

##### New Member
Re: IF formula help

Hey so I posted below on a new thread but just found this thread hoping you can help

Hoping Toadstool will see this as he knows the history of what I have been trying to do. But if anyone can help I would be so grateful.

I am doing timesheets and have formulas in now that work for adding hours over multiple rows and then multiplying it by 1.5 and 2.

What I need now is I have it set up as below;

Start Time Break Finish time
08:00 1:00 17:00
09:00 00:30 17:00

With the break column if I type in 00:30 it works if I type :30 then it doesn't recognize it and the formulas surrounding it don't work or if I was to type in .5 it won't recognise that either.

I need to get the break time to convert when it is typed in into time format but even if I format the cells it isn't working. This should be so simple but it just isn't working for me. I am using Google Excel Sheets

Help would be appreciated

Replies
2
Views
85
Replies
4
Views
75
Replies
8
Views
127
Replies
25
Views
390
Replies
9
Views
184

1,130,212
Messages
5,640,897
Members
417,177
Latest member
njosh

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