Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 27

Thread: IF formula

  1. #11
    New Member
    Join Date
    Sep 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #12
    New Member
    Join Date
    Sep 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  3. #13
    New Member
    Join Date
    Sep 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #14
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    228
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  5. #15
    New Member
    Join Date
    Sep 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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


  6. #16
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    228
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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
    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,"")
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  7. #17
    New Member
    Join Date
    Sep 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  8. #18
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    228
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  9. #19
    New Member
    Join Date
    Sep 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF formula help

    I have already locked it.

    Really appreciate all your help with this.

  10. #20
    New Member
    Join Date
    Sep 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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