# 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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### 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
1
Views
151
Replies
1
Views
40
Replies
2
Views
178
Replies
2
Views
58
Replies
26
Views
2K

1,127,519
Messages
5,625,278
Members
416,086
Latest member
CaptainGD

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