Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: IF formula

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

    Default IF formula

    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

  2. #2
    Board Regular
    Join Date
    Sep 2012
    Posts
    123
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

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

    Default 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-...ml#post5336244

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


    E F G H I J K L
    3 Start Time Break End Time Cap at 8 8 to 10 x 1.5 Over10 x 2 Hours Worked
    4 9:00 1:00 18:00 8 0 0 8
    5 8:30 1:00 19:30 8 3 0 10
    6 7:00 1:00 21:00 8 3 6 13
    7 10:00 2:00 16:00 4 0 0 4
    Nunya1

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

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

    Default Re: IF formula help

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

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

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






    Day Site Name Job No Date Start Time Break Finish Time Normal 1.5 Time 2.0 Time On Call TOIL Taken Public Holiday Sick Leave Annual Leave $70.00 $130.00 $195.00
    Monday 1-Jan-19 07:00am 09:00pm 8.00 2.00 8.00
    09:00am 12:00pm 3.00 FALSE 0.00
    12:00pm 01:00 04:00pm 3.00 FALSE 0.00
    Tuesday
    Wednesday
    Thursday

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

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


    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 OT 1 OT 2
    2 Monday 01-Jan-19 7:00 21:00 14.00
    3 01-Jan-19 9:00 12:00 3.00
    4 01-Jan-19 12:00 1:00 16:00 3.00 8.00 1.33 10.67
    5 Tuesday 02-Jan-19 8:30 1:00 17:00 7.50
    6 02-Jan-19 7:30 1:00 16:30 8.00 8.00 1.33 6.17
    7
    8 Wednesday 03-Jan-19 8:00 1:00 16:30 7.50 7.50
    9
    10 Thurday 04-Jan-19 9:00 1:00 17:00 7.00 7.00
    Nunya1 (2)

    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),"")
    K2 =IF(ISNUMBER(J2),SUMIFS(H:H,D:D,D2)-9.333333,"")
    Life advice found on a book of matches: "Keep cool. Keep away from children."

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

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

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

    Default Re: IF formula help

    Here you go...

    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 OT 1 OT 2
    2 Monday 01-Jan-19 7:00 21:00 14.00
    3 01-Jan-19 9:00 12:00 3.00
    4 01-Jan-19 12:00 1:00 16:00 3.00 8.00 2.00 21.33
    5 Tuesday 02-Jan-19 8:30 1:00 17:00 7.50
    6 02-Jan-19 7:30 1:00 16:30 8.00 8.00 2.00 12.33
    7
    8 Wednesday 03-Jan-19 8:00 1:00 16:30 7.50 7.50
    9
    10 Thurday 04-Jan-19 9:00 1:00 17:00 7.00 7.00
    Nunya1 (3)

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

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

    Default 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

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

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

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
  •