Help To Cap formulas to recognize hours reached and negative hours

[Nunya1]

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

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

 

[Nunya1]

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.

 

[Peter_SSs]

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]

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.
Everything matches in my spreadsheet the same as your example

 

[Nunya1]

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]

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

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]

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]

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]

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]

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