IF formula

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
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 :eek:

TIA
 

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
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
 

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
Joined
Sep 3, 2019
Messages
31
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
Joined
Sep 3, 2019
Messages
31
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,466
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 3, 2019
Messages
31

ADVERTISEMENT

Re: IF formula help

DaySite NameJob NoDateStart TimeBreakFinish TimeHoursNormal1.5 Time2.0 Time
Monday 01-Jan-197:00 21:0014.00
01-Jan-199:00 12:003.00
01-Jan-1912:001:0016:003.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>
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,466
Office Version
  1. 2016
Platform
  1. Windows
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.

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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,"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,466
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 3, 2019
Messages
31
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top