# Help To Cap formulas to recognize hours reached and negative hours

#### Nunya1

##### New Member
 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/19 X 6:30AM :30 3:30PM 8.5 8 .5 0 (3) 01/01/19 Y 4:30PM 08:30PM 4 4 (4) 01/01/19 Z 10:30AM 10:30PM 12 8 2 2 (5) 01/01/19 (6) 01/01/19 Night Shift (7) 01/01/19 N 6PM 3AM -14 14 Daily Total 01/01/19 8 2 14.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

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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### mrshl9898

##### Well-known Member
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
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.

#### Nunya1

##### New Member
Using that formula in K2 it now doesnt show any 2.0 time hours?

#### mrshl9898

##### Well-known Member

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

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

 E F G H I J K 1 Start Time (E) Break (F) Finish Time (G) Hours (H) Normal (I) 1.5 Time (J) 2.0 Time (K) 2 6:30 AM 0:30 3:30 PM 8.5 8 0.5 0 3 4:30 PM 8:30 PM 4 1.5 2.5 4 10:30 AM 10:30 PM 12 12 5 6 7 6:00 PM 3:00 AM 9 9 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;">
 E F G H I J K 1 Start Time (E) Break (F) Finish Time (G) Hours (H) Normal (I) 1.5 Time (J) 2.0 Time (K) 2 6:30 AM 0:30 3:30 PM 8.5 8 0.5 0 3 4:30 PM 8:30 PM 4 4 0 0 4 10:30 AM 10:30 PM 12 8 2 2 5 6 7 6:00 PM 3:00 AM 9 8 1 0 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
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)

Replies
8
Views
114
Replies
0
Views
50
Replies
1
Views
211
Replies
5
Views
65
Replies
1
Views
60

1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

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

### Which adblocker are you using?

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

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