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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
... 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)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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