Help To Cap formulas to recognize hours reached and negative hours

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Day (A)Date (B)Job (C)Site Name(D)Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
Monday (2)01/01/19X6:30AM:303:30PM8.58.50
(3)01/01/19Y4:30PM08:30PM44
(4)01/01/19Z10:30AM10:30PM12822
(5)01/01/19
(6)01/01/19
Night Shift (7)01/01/19N
6PM
3AM-14

14
Daily Total01/01/198214.5

<tbody>
</tbody>

Currently this is how my time sheet is set up.Night **** is highlighted red because at the moment it does not work in with any of the formulas I have in place.The normal hours need to cap at 8 the 1.5 time need to cap at 2 and the 2.0 time shows the remainder of the hours

So I will start with the first issue.

The first row is showing the correct hours. the formulas I have in the rows are:
  • =IF(E2<>"",(G2-E2-F2)*24,"") - Cell H for total hours - Which is fine
  • =IF(((G2-F2-E2)*24)>8,8,(G2-F2-E2)*24) - Cell I for normal hours
  • =IF(((G2-F2-E2)*24) > 8,MIN(((G2-F2-E2)*24),10)-8,"") - Cell J for 1.5 time
  • =IF(((G2-F2-E2)*24) > 10,((((G2-F2-E2)*24)-10)*1),"") - Cell K for 2.0 Time
  • Except for row 7 which is night shift & Daily Total
  • The Daily total formulas are;
  • =IF(AND(B8<>B9,B8<>""),MIN(SUMIFS(H:H,B:B,B8),8),"") - Normal Cell I
  • =IF(AND(ISNUMBER(I8),SUMIFS(H:H,B:B,B8)>8),MIN(SUMIFS(H:H,B:B,B8)-8,2),"") - 1.5 time Cell J
  • =IF(ISNUMBER(J8),MAX(0,(SUMIFS(H:H,B:B,B8)-10)*1),"") - 2.0 Time cell k
  • daily totals are currently reflecting what I need them to (excluding the night shift)


The second row is showing the correct hours BUT 1.5 of those should go to 1.5 time and the other 2.5 should go into 2.0 time
The third row should all go into 2.0 time

So I need to adjust the formulas so that they recognise that on the first row the 8 hours in normal has been reached and so forth 2 hours 1.5 time has been reached

I also need the night shift 6pm - 3am to work in with these formulas and calculate in with the daily total currently it comes up as a negative and then takes the hours off my total the night shift row hours need to be a positive and all of those hours should go into 2.0 time - What I do know is to get a negative to a positive it is =I7*-1 but I can't get it to work in with the formulas to recognise it. - I am not even sure if this is doubale?

Really appreciate any assistance with this.

Thanks,




Ps thank to someone on here for the initial formulas
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,752
Try:

H2 = =IF(OR(E2="",G2=""),"",IF(G2>E2,IF(E2<>"",(G2-E2-F2)*24,""),((1-E2)+G2-F2)*24))
I2 = =IF(H2="","",IF(H2>8,8,H2))
J2 = =IFERROR(IF(H2-I2>2,2,H2-I2),"")
K2 = =IFERROR(IF(SUM(I2:J2)<h2,h2-sum(i2:j2),""),"")< html=""></h2,h2-sum(i2:j2),""),"")<>
 
Last edited:

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Thanks Heaps that works for the night shift part

It still shows all the hours at normal and doesn't cap the normal hours at 8 then cap the 1.5 time at 2 then move the rest into the 2.0 time it does it correctly for the daily total but not for each row and I dont actually know if it is possible. However the problem is the guys see their timesheets and stress out because they see row 1-3 calculating at normal and not giving them the overtime if that makes sense even though it does say it at the bottom in the daily total.

They get paid 8 hours normal 2 hours x1.5 and the rest is double time.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,752

ADVERTISEMENT

Sorry, I didn't paste it in properly.

Code:
K2 = =IFERROR(IF(SUM(I2:J2)<h2,h2-sum(i2:j2),""),"")<h4,h4-sum(i4:j4),""),"")

<h4,h4-sum(i4:j4),""),"")

It's capping J at 2 for me... Can you paste in the results you get with the new formulas?

EDIT, still not pasting in, edit the below with the less than symbol

=IFERROR(IF(SUM(I2:J2) is less than H2,H2-SUM(I2:J2),""),"")</h4,h4-sum(i4:j4),""),"")
</h2,h2-sum(i2:j2),""),"")<h4,h4-sum(i4:j4),""),"")
 
Last edited:

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Yeah it is still not working normal time and 1.5 time is but the 2.0 time is showing nothing.

Using yours I think it needed the H column
so i did this and it worked =IFERROR(IF(H2-J2-I2>2,2,H2-J2-I2),"")

Really appreciate it - Do you know how to get for example row 3 to recognise that row 2 has already reached its 8 hours normal and .5 of its time an a half so that row 2 hours wont show in normal but 1.5hours would go into 1.5 time and the rest would go to 2.0 time and so forth?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,752

ADVERTISEMENT

Ahh, didn't realise you wanted to see a 0.

You'd need an identifier to show that the jobs are related, guessing this isn't date as 4 overlaps the time in 2 and 3, if you have an employee name or number then you could do something with SUMIF maybe...

I'd put in another table to get the total hours per person, then use the current formulas to get their breakdown into regular, 1.5 and 2.


So, add a column, employee name. As an example say it's in L.

Then list the unique names in N2 down.

O2 = =SUMIF(L:L,N2,H:H)
P2 = =IF(O2>8,8,O2)
Q2 = =MIN(O2-P2,2)
R2 = =O2-P2-Q2
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,444
Office Version
  1. 365
Platform
  1. Windows
See if these, copied down, do what you want.

Excel Workbook
EFGHIJK
1Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
26:30 AM0:303:30 PM8.580.50
34:30 PM8:30 PM4400
410:30 AM10:30 PM12822
5
6
76:00 PM3:00 AM9810
8
Split times
 
Last edited:

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Great thankyou.

What I need though and I don't know if it is possible as per your example row 3 I would need from the 4 hours - 1.5hours to go directly into 1.5time cell, and the the 2.5 hours to go straight into the 2 time cell.

so using your example this is how it would look (see below). The daily total row I still need to show as it does but i need it to recognise when the first 8 hours has been reached for normal, then when 2 hours has been reached for 1.5 and the rest into 2.0 time hopefully this makes sense



EFGHIJK
1Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
26:30 AM0:303:30 PM8.580.50
34:30 PM8:30 PM41.52.5
410:30 AM10:30 PM1212
5
6
76:00 PM3:00 AM99
8

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 94px;"><col style="width: 109px;"><col style="width: 74px;"><col style="width: 75px;"><col style="width: 87px;"><col style="width: 90px;"></colgroup><tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
EFGHIJK
1Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
26:30 AM0:303:30 PM8.580.50
34:30 PM8:30 PM4400
410:30 AM10:30 PM12822
5
6
76:00 PM3:00 AM9810
8

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 94px;"><col style="width: 109px;"><col style="width: 74px;"><col style="width: 75px;"><col style="width: 87px;"><col style="width: 90px;"></colgroup><tbody>
</tbody>
</body>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,444
Office Version
  1. 365
Platform
  1. Windows
What I need though and I don't know if it is possible as per your example row 3 I would need from the 4 hours - 1.5hours to go directly into 1.5time cell, and the the 2.5 hours to go straight into the 2 time cell.

so using your example this is how it would look (see below). The daily total row I still need to show as it does but i need it to recognise when the first 8 hours has been reached for normal, then when 2 hours has been reached for 1.5 and the rest into 2.0 time hopefully this makes sense
Ah, I didn't understand that very well, did I? :)

See if this is closer to the mark.

Excel Workbook
EFGHIJK
1Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
26:30 AM0:303:30 PM8.580.50
34:30 PM8:30 PM401.52.5
410:30 AM10:30 PM120012
5
6
76:00 PM3:00 AM9009
8
Split times (2)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,262
Messages
5,635,140
Members
416,843
Latest member
mrbrown91b

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