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

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

2b​

Worksheet Formulas Cell Formula R4 =SUM(B4:Q5)-R5 R5 =SUMPRODUCT(--(B4:Q5>8),(B4:Q5-8)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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