IF formula

[Nunya1]

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

TIA

 

[Nunya1]

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

Cheers for your help

 

[Nunya1]

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]

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

 

[Toadstool]

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]

Re: IF formula help

[TABLE="width: 1220"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Day[/TD]
[TD]Site Name[/TD]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Start Time[/TD]
[TD]Break[/TD]
[TD]Finish Time[/TD]
[TD]Hours[/TD]
[TD]Normal[/TD]
[TD]1.5 Time[/TD]
[TD]2.0 Time[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]01-Jan-19[/TD]
[TD]7:00[/TD]
[TD] [/TD]
[TD]21:00[/TD]
[TD]14.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]01-Jan-19[/TD]
[TD]9:00[/TD]
[TD] [/TD]
[TD]12:00[/TD]
[TD]3.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]01-Jan-19[/TD]
[TD]12:00[/TD]
[TD]1:00[/TD]
[TD]16:00[/TD]
[TD]3.00
[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]
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


[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Toadstool]

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).

Please check your column headings agree with those below.

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.

	A	B	C	D	E	F	G	H	I	J	K
Day	Site Name	Job No
Monday

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Break[/TD]
[TD="align: center"]Finish Time[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"]Normal[/TD]
[TD="align: center"]1.5 Time[/TD]
[TD="align: center"]2.0 Time[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]7:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]21:00[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]9:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]01-Jan-19[/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: center"]21.33[/TD]

</tbody>

Nunya5
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(E2<>"",(G2-E2-F2)*24,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=IF(AND(D2<>D3,D2<>""),MIN(SUMIFS(H:H,D:D,D2),8),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=IF(AND(ISNUMBER(I2),SUMIFS(H:H,D:D,D2)>8),MIN(SUMIFS(H:H,D:D,D2)-8,1.33333)*1.5,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2
[/TH]
[TD="align: left"]=IF(ISNUMBER(J2),(SUMIFS(H:H,D:D,D2)-9.333333)*2,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Nunya1]

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?

 

[Toadstool]

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]

Re: IF formula help

I have already locked it.

Really appreciate all your help with this.

 

[Nunya1]

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