# 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
When I copy and paste this into my spreadsheet it just brings up formula pulse error, however what you have is exactly how I want it to look. Thanks heaps for your help. I am just leaving work and will have another go tomorrow morning.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Peter_SSs

##### MrExcel MVP, Moderator
When I copy and paste this into my spreadsheet it just brings up formula pulse error, however what you have is exactly how I want it to look. Thanks heaps for your help. I am just leaving work and will have another go tomorrow morning.
If it doesn't work for you in the morning, please explain what you mean by "formula pulse error".

#### Nunya1

##### New Member
Ok so it worked however it is showing the wrong figures it seems to be rounding it. So instead of the total hours showing 8.5 in cell H2 it is showing 8.67 I think if I can work out why it is doing that then it should work.

#### Nunya1

##### New Member
 Start Time Break Finish Time Hours Normal 1.5 Time 2.0 Time 06:30am 00:30 3:30PM 8.67 8 .67 0 04:30pm 9:00pm 5.38 0 1.33 4.04

<tbody>
</tbody>

These are the figures it is giving me?

#### Peter_SSs

##### MrExcel MVP, Moderator

.. it seems to be rounding it.
The formula isn't rounding anything, more likely your times in Start, Break &/or Finish are showing as rounded.

Are those Start, Break, Finish times directly entered or are they the result of formulas, or copy data from elsewhere?

If directly entered or copied, select the 06:30am cell and look in the formula bar and see if it says exactly 6:30 AM there. Same for the other times.

#### Nunya1

##### New Member
They are in as data validation referring to another sheet hidden. The times are all in time format showing 6:30AM I will go through and double check now and see if I can find where it has gone wrong.

Thanks

#### Peter_SSs

##### MrExcel MVP, Moderator

They are in as data validation referring to another sheet hidden.
You would then need to look at those values in the hidden sheet.

#### Nunya1

##### New Member
I think it has something to do with the break but I cannot work it out. In the hidden sheet the formatting is identical to the cells in the start, break and finish. I am wondering if it has something to do with me formatting the break time as a time format rather then a number

#### Nunya1

##### New Member
If I use this formula =IF(OR(E2="",G2=""),"",IF(G2>E2,IF(E2<>"",(G2-E2-F2)*24,""),((1-E2)+G2-F2)*24)) in cell H2 (HOURS) and use all your other formulas it seems to do what i need it too.

HOWEVER if the first row for example Row 2 dosen't meet the 8 hours it then throw it all into double time

Sometimes the guys can work up to 6 different jobs in an 8 hour period I need it to recognise that no matter which line the normal has to be equal to 8 first then 2 hours to 1.5 time and then the rest to double bubble.

 Start Break Finish Hours Normal 1.5 Time 2.0Time 07:00am 10:00am 3 3 10:00am 00:30 03:30pm 5 5

<tbody>
</tbody>

Not sure what is going on

#### Peter_SSs

##### MrExcel MVP, Moderator
In the hidden sheet the formatting is identical to the cells in the start, break and finish.
It is not the formatting that you should be checking, but the underlying value in the cell.
In the hidden sheet, for one of the rows where the total hours seems to be wrong, like the first data row in post 14, suppose the start time is showing as 06:30am as in that post 14 example. Manually put in a vacant cell (say X1) in that worksheet 06:30 am. Now in Y1 put the formula =X1 and format Y1 as Number (not time) with 8 decimal places. Now in Z1 put the formula =A2 (where A2 is whatever cell had that original 06:30 am start time) and format Z1 as Number with 8 decimal places. Are Y1 and Z1 identical?
You may need to do the same sort of exercise for the Break time and Finish time.

If I use this formula =IF(OR(E2="",G2=""),"",IF(G2>E2,IF(E2<>"",(G2-E2-F2)*24,""),((1-E2)+G2-F2)*24)) in cell H2 (HOURS) and use all your other formulas it seems to do what i need it too.
I don't know what this is about. It seems to be a completely different issue as the problem you reported before was the wrong hours appearing in column D and this formula doesn't have anything to do with column D or the original data in columns A:C

Last edited:

Replies
1
Views
137
Replies
2
Views
174
Replies
2
Views
56
Replies
26
Views
2K
Replies
8
Views
102

1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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