I need help creating an excel fomula

[rosyposy]

I created a spreadsheet years ago to track employee hours, and now we need to add overtime to it. I'm hoping I can do this and have it calculate into one cell per employee. The spreadsheet is done monthly and I have just one sheet with everyone on it and then a sheet for each employee for their stub.

On the monthly sheet, I put in the total hours daily and the all add up into one cell and then the pay is calculated. If the house are over 8 we have to pay overtime, so I am hoping that there is a formula that I can gather the over 8 hours for each day and have it total into one cell.

I hope this makes sense and someone can help me

 

[Gerald Higgins]

Hi, welcome to the board.

I'm sure this is do-able but your request is far too vague for people (well me at least) to answer it definitively.

Can you describe how your data is laid out - maybe post a small sample - and describe clearly exactly what you want to do with it ?

 

[Twollaston]

I created a spreadsheet years ago to track employee hours, and now we need to add overtime to it. I'm hoping I can do this and have it calculate into one cell per employee. The spreadsheet is done monthly and I have just one sheet with everyone on it and then a sheet for each employee for their stub.

On the monthly sheet, I put in the total hours daily and the all add up into one cell and then the pay is calculated. If the house are over 8 we have to pay overtime, so I am hoping that there is a formula that I can gather the over 8 hours for each day and have it total into one cell.

I hope this makes sense and someone can help me

This is vague, but for example if the hours for today are in cell A1, and I worked 9 hours today then A1=9. In A2(or any cell) you can put =if(A1>8, A1-8, 0) and this basically says, if A1 is more than 8 then take the value of A1(9) and subtract 8 from it. So this would return 1, as in 1 over time hour. If the amount is 8 or less it will return 0 hours because there are no overtime hours for that day. And then you can sum them all up for each day with a sum formula.

 

[SimpliXL]

This is vague, but for example if the hours for today are in cell A1, and I worked 9 hours today then A1=9. In A2(or any cell) you can put =if(A1>8, A1-8, 0) and this basically says, if A1 is more than 8 then take the value of A1(9) and subtract 8 from it. So this would return 1, as in 1 over time hour. If the amount is 8 or less it will return 0 hours because there are no overtime hours for that day. And then you can sum them all up for each day with a sum formula.

And, likewise, if you needed to report the regular hours in addition to the overtime, you could use something like [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(A1<8,A1,8).[/FONT]

 

[rosyposy]

Hi, welcome to the board.

I'm sure this is do-able but your request is far too vague for people (well me at least) to answer it definitively.

Can you describe how your data is laid out - maybe post a small sample - and describe clearly exactly what you want to do with it ?

Thank you. Here is the url https://imgur.com/a/G1HzI4B which shows a picture of my spreadsheet (I apologize, I couldn't figure out how to place it in my question).

Currently I have cells B4 to Q4 & B5 to P5 totalling up into R4.

What I would like to do is unmerge R4 & R5 and use R4 for regular hours (8 hours) and R5 for overtime hours (anything over 8 hours).

So, if it's at all possible I need a formula that would take all the hours over 8 for every day and total it up into R5.

Thank you all for your help so far. I hope this makes more sense.

 

[Dave Patton]

N.B. I hide some of the columns.
The formulas show the relevant ranges.
I hope that this helps

Please ensure that you specify the complete rules for calculating overtime.

Excel 2010
	A	B	K	L	M	N	O	P	Q	R	S	T
1	Name	1	10	11	12	13	14	15	16	Total	OT	Reg
2		17	26	27	28	29	30	31
3	Empl 1	8	8	11.5	8	8	8	8
4			8	11.5	6	8	8			236	18	218
5
6	or									245
2b

Cell Formulas
Range	Formula
R4	=SUM(B3:Q4)
R6	=SUM(B3:Q4)+SUMPRODUCT(--(B3:Q4>8),(B3:Q4-8))/2
S4	=SUMPRODUCT(--(B3:Q4>8),(B3:Q4-8))
T4	=R4-S4

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	Name	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	Total	Hrs
3		17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
4	Empl 1	8	8	8	9.5	9.5	8	8	10.5	8	8	11.5	8	8	8	8		218	Reg
5			4	9.5	8	9.5	8	8	10.5	8	8	11.5	6	8	8			18	OT
2b

Cell Formulas
Range	Formula
R4	=SUM(B4:Q5)-R5
R5	=SUMPRODUCT(--(B4:Q5>8),(B4:Q5-8))

 

[rosyposy]

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
Name	Total	Hrs
Empl 1	Reg
OT

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10.5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11.5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]218[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10.5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11.5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]

</tbody>

2b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R4[/TH]
[TD="align: left"]=SUM(B4:Q5)-R5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R5[/TH]
[TD="align: left"]=SUMPRODUCT(--(B4:Q5>8),(B4:Q5-8))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

THANK YOU THANK YOU THANK YOU DAVE!! It works perfectly!! I am so happy that you figured this out for me.....and it's so simple. I really appreciate you taking the time to help me

 

[Dave Patton]

Thanks for the feedback. Dave