# 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

#### Nunya1

##### New Member
Thank you I realised what was going wrong and have fixed it. What I am hoping will be my last issue with them is that the night shift calculates straight into double time hours which is great and what i need but in the daily total hours if the cell I has not reached its 8 it is making up the night shift hours and turning them into normal hours which is wrong night shift will always be doublt time

For example the guys might work 7-10am and then 9pm until 2am

That whole night shift should calculate into the daily totals as time and a half but because of the formula I have in there it is trying to get me 8 normal hours first so anything that goes into row16 is double time

The daily totals I have already will be referring to cell B which is date

Daily totals sit in Row 17

Cell I - Normal =IF(AND(B17<>B18,B17<>""),MIN(SUMIFS(H:H,B:B,B17),8),"")

Cell J - 1.5 Time =IF(AND(ISNUMBER(I17),SUMIFS(H:H,B:B,B17)>8),MIN(SUMIFS(H:H,B:B,B17)-8,2),"")

Cell K - 2.0 Time =IF(ISNUMBER(J17),MAX(0,(SUMIFS(H:H,B:B,B17)-10)*1),"")

So I need to tell it that if there is hours in Row 16 they need to automatically go into double time in row 17 (Daily Totals)

Hope this makes sense thanks for your help

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Peter_SSs

##### MrExcel MVP, Moderator
... night shift will always be doublt time

That whole night shift should calculate into the daily totals as time and a half
This sounds contradictory to me. One staes night shift should be double time, the other states that night shift should be time & a half.

See if this is any closer, night shift goes into double time.

Excel Workbook
ABCDEFGHIJK
1Day (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)
2Monday (2)1/01/2019X6:30 AM0:303:30 PM8.580.50
3-31/01/2019Y4:30 PM8:30 PM401.52.5
4-41/01/2019Z10:30 AM10:30 PM120012
5-51/01/2019
6-61/01/2019
7Night Shift (7)1/01/2019N6:00 PM3:00 AM99
Split times (2)

Replies
8
Views
118
Replies
0
Views
70
Replies
1
Views
217
Replies
5
Views
66
Replies
1
Views
64

1,130,091
Messages
5,640,059
Members
417,125
Latest member
sfreind

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