Fix timesheet totals for regular time and overtime, Maybe Macro

[zone709]

Hi if anyone can help.

I update the sheets below because, If a guy works over 40 hours he gets overtime regardless if he works 10 hours in one day. You can see on the first sheet column Y is adding up to 48. Since its over 40 i have to break it down as you can see on second sheet. You can see 8/2 then 6 stays the same because its under 8. Then 8/4 for Wednesday then 8/2 for Thursday then Friday stays the same because the Y Column total reached 40. So Friday he doesn't get 8/2. Friday stays at 10.
The second guy only worked 39 hours this week. Since its under 40 nothing happens. I would have this time down the whole sheet. Trying to See maybe a Macro would be good for this or a formula but I wouldn't really know where would the formula go on the Sheet. Why was thinking a macro. The columns with numbers that would change only are F,G I,J L,M O,P R,S U,V W,X . Ignore all DT columns. They are never used.

Sample Sheet

Timesheet 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
5						Monday			Tuesday			Wednesday			Thursday			Friday			Saturday		Sunday		WEEKLY TOTALS
6						2/27			2/28			3/1			3/2			3/3			3/4		3/5
7	No.	N	T	ID	J	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	ST	OT	ST	OT	DT
8	1	Bob	7 Super	100	253	10.0			6.0			12.0			10.0			10.0							48.0	-
9	2	Jason1	7 Super	190	896	9.0			9.0			5.0			13.0			3.0							39.0	-
Timesheet

Cells with Data Validation
Cell	Allow	Criteria
C8:C9	List	=$AH$844:$AH$944

Results

Timesheet Test 2.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
5						Monday			Tuesday			Wednesday			Thursday			Friday			Saturday		Sunday		WEEKLY TOTALS
6						2/27			2/28			3/1			3/2			3/3			3/4		3/5
7	No.	N	T	ID	J	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	DT	ST	OT	ST	OT	ST	OT	DT
8	1	Bob	7 Super	5	253	8.0	2.0		6.0			8.0	4.0		8.0	2.0		10.0							40.0	8.0
9	2	Jason1	7 Super	9	896	9.0			9.0			5.0			13.0			3.0							39.0	-
Timesheet

Cells with Data Validation
Cell	Allow	Criteria
C8:C9	List	=$AH$844:$AH$944

 

[Bosquedeguate]

In your example some days there are ST hours and OT hours, But another day with 10 ST hours why?
I can see what you are doing, but there does not appear to be an algorithm for ST and OT hours on a daily basis. Please explain

I would probably put a formula in your daily hours worked cells

 

[zone709]

Hopefully this helps.

I get the sheets with ST Day hours but i have to break them down as you can see on the results sheet to Reg time and OT. Only if the person works over 40 hours for the week.

Since the first guy worked 48 hours - =SUM(F8,I8,L8,O8,R8,U8,W8) formula is in column Y - I break it down on the results sheet i posted to show you. I am always going from Monday first to Friday on the breakdown.

You can see me breaking it down on results sheey Monday 8 / 2 - Tuesday i leave alone because its under 8 hours. - Then Wednesday 8 / 4 - Thursday 8 / 2 . - Friday i leave as 10 and don't break it out to 8 / 2 is because in column Y i reached 40 hours now. Lets say I broke down Friday also. Then it would be 38 / 10 which is not good because there is no OT until over 40 hours. Hope this helps. Reason why i am trying to make this easier is because i usually have a sheet like this with 150 people going down. I then have to breakdown everyone if there over 40 hours and takes a lot of time. So i am trying to do something like this to help me out.

 

[Bosquedeguate]

Oops! Response not for this question.

 

[Bosquedeguate]

Ignore that post .... Sorry

 

[zone709]

Ignore that post .... Sorry

ok

 

[Bosquedeguate]

Hopefully this helps.

I get the sheets with ST Day hours but i have to break them down as you can see on the results sheet to Reg time and OT. Only if the person works over 40 hours for the week.

Since the first guy worked 48 hours - =SUM(F8,I8,L8,O8,R8,U8,W8) formula is in column Y - I break it down on the results sheet i posted to show you. I am always going from Monday first to Friday on the breakdown.

You can see me breaking it down on results sheey Monday 8 / 2 - Tuesday i leave alone because its under 8 hours. - Then Wednesday 8 / 4 - Thursday 8 / 2 . - Friday i leave as 10 and don't break it out to 8 / 2 is because in column Y i reached 40 hours now. Lets say I broke down Friday also. Then it would be 38 / 10 which is not good because there is no OT until over 40 hours. Hope this helps. Reason why i am trying to make this easier is because i usually have a sheet like this with 150 people going down. I then have to breakdown everyone if there over 40 hours and takes a lot of time. So i am trying to do something like this to help me out.

OK ... So total hours must be greater than 40 hours for any OT hours apply.
So if our SUM(hours) > 40, Then split out the OT hours, both at the right-hand totals cells, but also on a daily basis? Right?

Are you entering hours daily or at the end of the week? You really don't know until the weekly hours are totaled if you have to split out daily ST and OT hours.

Or is a daily break-down necessary. Just going by totals ... if Joe works 4) 10 hours days in a week. No OT? But if he works 4) 10 hour days and 1) 45 hour days (45 total) he will be paid for 40 ST hours and 5 OT hours. Is a daily break-down needed?

 

[zone709]

So if our SUM(hours) > 40, Then split out the OT hours, both at the right-hand totals cells, but also on a daily basis? Right? Yes Well Column Y & Z change because the daily numbers are changing when its broken down.

Are you entering hours daily or at the end of the week? End of Week You really don't know until the weekly hours are totaled if you have to split out daily ST and OT hours. I get the sheet at the end of week.

Or is a daily break-down necessary. Yes it is. Just going by totals ... if Joe works 4) 10 hours days in a week. No OT? But if he works 4) 10 hour days and 1) 45 hour days (45 total) he will be paid for 40 ST hours and 5 OT hours. Is a daily break-down needed? Yes

If a guy works Monday to Thursday 10 a day and the total is 40. Nothing needs to be done

If we works Monday to Thursday 10 hour a day. Then Friday 5 hours. Then again i start from Monday changing it. It would be Mon 8 / 2 -Tuesday 8 / 2 - Wednesday 9 / 1 - Thursday 10 - Friday 5
My breaking down will always start on Monday until i hit 40 in Y then i stop.

 

[Bosquedeguate]

Do you have a timesheet (Excel Worksheet) for each week? I am think about VBA code to adjust the timesheet. But, I have know what is the range of cells that will be operated on (e.g. an Excel tab), or do you have multiple weeks on one worksheet?

 

[zone709]

I a sample sheet for you. every week is different more range less range going downward. How would i post the sheet Xl2bb or another way?