Help To Cap formulas to recognize hours reached and negative hours

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
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/19X6:30AM:303:30PM8.58.50
(3)01/01/19Y4:30PM08:30PM44
(4)01/01/19Z10:30AM10:30PM12822
(5)01/01/19
(6)01/01/19
Night Shift (7)01/01/19N
6PM
3AM-14

14
Daily Total01/01/198214.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

New Member
Joined
Sep 3, 2019
Messages
31
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.
 

Some videos you may like

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
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 3, 2019
Messages
31
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

New Member
Joined
Sep 3, 2019
Messages
31
Start TimeBreakFinish TimeHoursNormal1.5 Time2.0 Time
06:30am00:303:30PM8.678.670
04:30pm9:00pm5.3801.334.04

<tbody>
</tbody>


These are the figures it is giving me?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

.. 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
Joined
Sep 3, 2019
Messages
31
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
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 3, 2019
Messages
31
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
Joined
Sep 3, 2019
Messages
31
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.

StartBreakFinishHoursNormal1.5 Time2.0Time
07:00am10:00am33
10:00am00:3003:30pm55

<tbody>
</tbody>

Not sure what is going on
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top