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

Some videos you may like

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

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)
 

Watch MrExcel Video

Forum statistics

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