Trying to use a Macro to fix timesheet numbers for regular time and overtime.

[zone709]

Hi, I have hours for the week from F to X. Y and Z i have formulas inside to add up the week. The thing is if someone goes over 40 hours for the week then they get the overtime.
If its under 40 hours for the week. Then nothing happens to that row and you can see below in results Mike stays the same.
What i am doing now is i have to go through each person and break it out. I would like maybe a macro to do this as there are so many more people. You can see in the results how i break it down starting with the first day. like Jeff on Tuesday is 8 and 2. Marco Monday 8 and 2 Tuesday 8 and 2. All the ST columns shouldn't go over 8. The rest goes into overtime for that day as long as Y is over 40. Once Y hits 40 that's it. Row stays the same.

In short version. I need to break each person out to get overtime as long as they go over 40 hours for the week. If not then that row stays the same. DT in yellow or not yellow doesn't get touched ever. it will always be blank. No one does double time. Any help would be great.

Sheet 1

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1						Monday			Tuesday			Wednesday			Thursday			Friday			Saturday		Sunday		WEEKLY TOTALS
2
3	No.		Trade	ID	JOB	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	ST	OT	ST	OT	DT
4	1	Jeff	Super	45	152 23rd Street	5.0			10.0			8.0			8.0			13.0			2.0		8.0		54.0	-	-
5	2	Marco	Operator	63	152 23rd Street	10.0			10.0			9.0			6.0			8.0			4.0				47.0	-	-
6	3	Mike	Operator	121	152 23rd Street	6.0			8.0			6.0			8.0			5.0							33.0	-	-
Data

Results

Results.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1						Monday			Tuesday			Wednesday			Thursday			Friday			Saturday		Sunday		WEEKLY TOTALS
2
3	No.		Trade	ID	JOB	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	ST	OT	ST	OT	DT
4	1	Jeff	Super	45	152 23rd Street	5.0			8.0	2.0		8.0			8.0			8.0	5.0			2.0	3.0	5.0	40.0	14.0	-
5	2	Marco	Operator	63	152 23rd Street	8.0	2.0		8.0	2.0		8.0	1.0		6.0			8.0			2.0	2.0			40.0	7.0	-
6	3	Mike	Operator	121	152 23rd Street	6.0			8.0			6.0			8.0			5.0							33.0	-	-
Data

 

[kvsrinivasamurthy]

Macro not required.
In Y4

=MIN(40,SUMPRODUCT($F4:$X4*($F$3:$X$3<>"DT")))

In Z4

=MIN(40,SUMPRODUCT($F4:$X4*($F$3:$X$3<>"DT")))

Both copied down.

 

[myall_blues]

If you want the hours to break out automatically I'd suggest adding a block of columns (F-L in example show) where you enter the total hours for each day. Formulas can then calculate the standard time and overtime for each day and the weekly totals.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1						Total Hours							Monday			Tuesday			Wednesday			Thursday			Friday			Saturday		Sunday		WEEKLY TOTALS
2
3	No.		Trade	ID	JOB	Mon	Tue	Wed	Thu	Fri	Sat	Sun	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	ST	OT	ST	OT	DT
4	1	Jeff	Super	45	152 23rd Street	5	10	8	8	13	2	8	5			8	2		8			8			8	5		2		8		47	7	0
5	2	Marco	Operator	63	152 23rd Street	10	10	9	6	8	4		8	2		8	2		8	1		6			8			4				42	5	0
6	3	Mike	Operator	121	152 23rd Street	6	8	6	8	5			6			8			6			8			5							33	0	0
Sheet1

Cell Formulas
Range	Formula
M4:M6	M4	=IF(F4="","",MIN(F4,8))
N4:N6	N4	=IF(F4>8,F4-8,"")
P4:P6	P4	=IF(G4="","",MIN(G4,8))
Q4:Q6	Q4	=IF(G4>8,G4-8,"")
S4:S6	S4	=IF(H4="","",MIN(H4,8))
T4:T6	T4	=IF(H4>8,H4-8,"")
V4:V6	V4	=IF(I4="","",MIN(I4,8))
W4:W6	W4	=IF(I4>8,I4-8,"")
Y4:Y6	Y4	=IF(J4="","",MIN(J4,8))
Z4:Z6	Z4	=IF(J4>8,J4-8,"")
AB4:AB6	AB4	=IF(K4="","",MIN(K4,8))
AC4:AC6	AC4	=IF(K4>8,K4-8,"")
AD4:AD6	AD4	=IF(L4="","",MIN(L4,8))
AE4:AE6	AE4	=IF(L4>8,L4-8,"")
AF4:AG6	AF4	=SUM(M4,P4,S4,V4,Y4,AB4,AD4)
AH4:AH6	AH4	=SUM(O4,R4,U4,X4,AA4,)

 

[zone709]

Macro not required.
In Y4

=MIN(40,SUMPRODUCT($F4:$X4*($F$3:$X$3<>"DT")))

In Z4

=MIN(40,SUMPRODUCT($F4:$X4*($F$3:$X$3<>"DT")))

Both copied down.

Hi thanks for reply. Do i put each formula in Y and then Z. Both are returning 40?

 

[kvsrinivasamurthy]

Sorry, it was a mistake.
In Z4, copy down.

=MAX(0,SUMPRODUCT($F4:$X4*($F$3:$X$3<>"DT"))-40)

 

[zone709]

Hi thanks for reply. Do i put each formula in Y and then Z. Both are returning 40?

Hi ok thanks. I see what it does. It only changes Column Y and Z which is what i am looking for but i need the break outs in each column for ST and OT.

My data comes all into Column under ST.

So if its 10 or 11 and as long as Y is over 40 then i need to break it out like this 8 / 2 or 8 / 3.
The option response above from Myall is pretty close to what i need but i am trying to stay away from adding columns from F to L. Then run formulas. Reason i already have formulas pulling the numbers into ST only.

 

[myall_blues]

i am trying to stay away from adding columns from F to L. Then run formulas. Reason i already have formulas pulling the numbers into ST only.

Without a macro you can’t put it in to a cell and then have that cell change. I.e. you can’t put 10 in a cell and have it change itself to 8 and carry over 2. Even with a macro it’s not advisable.
You could put the data entry table on the end- it doesn’t have to go in F to L. Or even on another tab. Excel has lots of real estate- you may as well use it.

 

[zone709]

Without a macro you can’t put it in to a cell and then have that cell change. I.e. you can’t put 10 in a cell and have it change itself to 8 and carry over 2. Even with a macro it’s not advisable.
You could put the data entry table on the end- it doesn’t have to go in F to L. Or even on another tab. Excel has lots of real estate- you may as well use it.

I been thinking about this. I may make a macro step by step. Put the data entry table on another tab. Work the formulas for the results and so on. I will probably Record alot then Re write it.

 

[myall_blues]

I been thinking about this. I may make a macro step by step. Put the data entry table on another tab. Work the formulas for the results and so on. I will probably Record alot then Re write it.

You don’t need a macro to do that - it can all be done with formulas. Just cut columns F to L from my response and paste them on another tab.

 

[zone709]

Hi, I am testing the formulas and building the sheet. The first sheet below is the total numbers i get to start right.
The second sheet with the formulas does well but look at Wednesday. It goes 8 and 1. On the results sheet. I need it to just stay 9 because as you can see in the ST column this guy never went over 40 hours for the week. So technically he doesn't get Overtime. The total in the ST column should be 37 instead of 36 and 1. If its over 40 hours then yes that would be right. Anyway updating this formula so it works that way? thanks.

Test.xlsx
	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
7	NO	Name	Trade	ID	Job	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
8	1	Joe	Super	12	0	6	8	9	6	8
Data

Results

Test.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
5	Monday			Tuesday			Wednesday			Thursday			Friday			Saturday		Sunday		WEEKLY TOTALS
6	7/22			7/23			7/24			7/25			7/26			7/27		7/28
7	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	ST	OT	ST	OT	DT
8	6.0			8.0			8.0	1.0		6.0			8.0							36.0	1.0
Data