Thread: IF formula Thanks:  7 Post #5336772 (1)Post #5338008 (1)Post #5336777 (1)Post #5342764 (1)Post #5337968 (1) Likes:  2 Post #5337968 (1)Post #5337996 (1)

1. 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  Reply With Quote

2. 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  Reply With Quote

3. 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  Reply With Quote

4. 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.  Reply With Quote

5. 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  Reply With Quote

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

A B C D E F G H I J K
1 Day Site Name Job No Date Start Time Break Finish Time Hours Normal 1.5 Time 2.0 Time
2 Monday 01-Jan-19 7:00 21:00 14
3 01-Jan-19 9:00 12:00 3
4 01-Jan-19 12:00 1:00 16:00 3 8 2.00 21.33
Nunya5

Worksheet Formulas
Cell Formula
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,"")  Reply With Quote

7. 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?  Reply With Quote

8. 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.  Reply With Quote

9. Re: IF formula help

I have already locked it.

Really appreciate all your help with this.  Reply With Quote

10. 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  Reply With Quote

User Tag List

Tags for this Thread

cell, double, formula, hours, time  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•