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
 

Some videos you may like

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.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,194
Try:

H2 = =IF(OR(E2="",G2=""),"",IF(G2>E2,IF(E2<>"",(G2-E2-F2)*24,""),((1-E2)+G2-F2)*24))
I2 = =IF(H2="","",IF(H2>8,8,H2))
J2 = =IFERROR(IF(H2-I2>2,2,H2-I2),"")
K2 = =IFERROR(IF(SUM(I2:J2)<h2,h2-sum(i2:j2),""),"")< html=""></h2,h2-sum(i2:j2),""),"")<>
 
Last edited:

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Thanks Heaps that works for the night shift part

It still shows all the hours at normal and doesn't cap the normal hours at 8 then cap the 1.5 time at 2 then move the rest into the 2.0 time it does it correctly for the daily total but not for each row and I dont actually know if it is possible. However the problem is the guys see their timesheets and stress out because they see row 1-3 calculating at normal and not giving them the overtime if that makes sense even though it does say it at the bottom in the daily total.

They get paid 8 hours normal 2 hours x1.5 and the rest is double time.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,194
Sorry, I didn't paste it in properly.

Code:
K2 = =IFERROR(IF(SUM(I2:J2)<h2,h2-sum(i2:j2),""),"")<h4,h4-sum(i4:j4),""),"")
<h4,h4-sum(i4:j4),""),"")

It's capping J at 2 for me... Can you paste in the results you get with the new formulas?

EDIT, still not pasting in, edit the below with the less than symbol

=IFERROR(IF(SUM(I2:J2) is less than H2,H2-SUM(I2:J2),""),"")</h4,h4-sum(i4:j4),""),"")
</h2,h2-sum(i2:j2),""),"")<h4,h4-sum(i4:j4),""),"")
 
Last edited:

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Yeah it is still not working normal time and 1.5 time is but the 2.0 time is showing nothing.

Using yours I think it needed the H column
so i did this and it worked =IFERROR(IF(H2-J2-I2>2,2,H2-J2-I2),"")

Really appreciate it - Do you know how to get for example row 3 to recognise that row 2 has already reached its 8 hours normal and .5 of its time an a half so that row 2 hours wont show in normal but 1.5hours would go into 1.5 time and the rest would go to 2.0 time and so forth?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,194
Ahh, didn't realise you wanted to see a 0.

You'd need an identifier to show that the jobs are related, guessing this isn't date as 4 overlaps the time in 2 and 3, if you have an employee name or number then you could do something with SUMIF maybe...

I'd put in another table to get the total hours per person, then use the current formulas to get their breakdown into regular, 1.5 and 2.


So, add a column, employee name. As an example say it's in L.

Then list the unique names in N2 down.

O2 = =SUMIF(L:L,N2,H:H)
P2 = =IF(O2>8,8,O2)
Q2 = =MIN(O2-P2,2)
R2 = =O2-P2-Q2
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,659
Office Version
365
Platform
Windows
See if these, copied down, do what you want.

Excel Workbook
EFGHIJK
1Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
26:30 AM0:303:30 PM
8.5
8
0.5
0
34:30 PM8:30 PM4400
410:30 AM10:30 PM12822
5
6
76:00 PM3:00 AM9810
8
Split times
 
Last edited:

Nunya1

New Member
Joined
Sep 3, 2019
Messages
31
Great thankyou.

What I need though and I don't know if it is possible as per your example row 3 I would need from the 4 hours - 1.5hours to go directly into 1.5time cell, and the the 2.5 hours to go straight into the 2 time cell.

so using your example this is how it would look (see below). The daily total row I still need to show as it does but i need it to recognise when the first 8 hours has been reached for normal, then when 2 hours has been reached for 1.5 and the rest into 2.0 time hopefully this makes sense



EFGHIJK
1Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
26:30 AM0:303:30 PM8.580.50
34:30 PM8:30 PM41.52.5
410:30 AM10:30 PM1212
5
6
76:00 PM3:00 AM99
8

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 94px;"><col style="width: 109px;"><col style="width: 74px;"><col style="width: 75px;"><col style="width: 87px;"><col style="width: 90px;"></colgroup><tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
EFGHIJK
1Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
26:30 AM0:303:30 PM8.580.50
34:30 PM8:30 PM4400
410:30 AM10:30 PM12822
5
6
76:00 PM3:00 AM9810
8

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 101px;"><col style="width: 94px;"><col style="width: 109px;"><col style="width: 74px;"><col style="width: 75px;"><col style="width: 87px;"><col style="width: 90px;"></colgroup><tbody>
</tbody>
</body>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,659
Office Version
365
Platform
Windows
What I need though and I don't know if it is possible as per your example row 3 I would need from the 4 hours - 1.5hours to go directly into 1.5time cell, and the the 2.5 hours to go straight into the 2 time cell.

so using your example this is how it would look (see below). The daily total row I still need to show as it does but i need it to recognise when the first 8 hours has been reached for normal, then when 2 hours has been reached for 1.5 and the rest into 2.0 time hopefully this makes sense
Ah, I didn't understand that very well, did I? :)

See if this is closer to the mark.

Excel Workbook
EFGHIJK
1Start Time (E)Break (F)Finish Time (G)Hours (H)Normal (I)1.5 Time (J)2.0 Time (K)
26:30 AM0:303:30 PM
8.5
8
0.5
0
34:30 PM8:30 PM401.52.5
410:30 AM10:30 PM120012
5
6
76:00 PM3:00 AM9009
8
Split times (2)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,162
Messages
5,467,000
Members
406,516
Latest member
richcresswell

This Week's Hot Topics

Top