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

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1MondayTuesdayWednesdayThursdayFridaySaturdaySundayWEEKLY TOTALS
2
3No.TradeIDJOBSTOTDTSTOTDTSTOTDTSTOTDTSTOTDTSTOTSTOTSTOTDT
41JeffSuper45152 23rd Street5.010.08.08.013.02.08.054.0--
52MarcoOperator63152 23rd Street10.010.09.06.08.04.047.0--
63MikeOperator121152 23rd Street6.08.06.08.05.033.0--
Data


Results

Results.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1MondayTuesdayWednesdayThursdayFridaySaturdaySundayWEEKLY TOTALS
2
3No.TradeIDJOBSTOTDTSTOTDTSTOTDTSTOTDTSTOTDTSTOTSTOTSTOTDT
41JeffSuper45152 23rd Street5.08.02.08.08.08.05.02.03.05.040.014.0-
52MarcoOperator63152 23rd Street8.02.08.02.08.01.06.08.02.02.040.07.0-
63MikeOperator121152 23rd Street6.08.06.08.05.033.0--
Data
 
Sort by date Sort by votes
Try these formulas. Note they've all changed (columns F:AA).

Book1
FGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
5MondayTuesdayWednesdayThursdayFridaySaturdaySundayWEEKLY TOTALS
6       
7STOTDTSTOTDTSTOTDTSTOTDTSTOTDTSTOTSTOTSTOTDTNONameTradeIDJobMondayTuesdayWednesdayThursdayFridaySaturdaySunday
8608090608000003701JoeSuper1268968
Sheet1
Cell Formulas
RangeFormula
F6F6=IF($M$2="","",$M$2-6)
I6I6=IF($M$2="","",$M$2-5)
L6L6=IF($M$2="","",$M$2-4)
O6O6=IF($M$2="","",$M$2-3)
R6R6=IF($M$2="","",$M$2-2)
U6U6=IF($M$2="","",$M$2-1)
W6W6=IF($M$2="","",$M$2-0)
F8,U8,W8,R8,O8,L8,I8F8=LET(hours,XLOOKUP(F$5,$AG$7:$AM$7,$AG8:$AM8),IF(hours<=40,hours,MIN(8,hours)))
G8,V8,X8,S8,P8,M8,J8G8=LET(hours,XLOOKUP(F$5,$AG$7:$AM$7,$AG8:$AM8),IF(hours<=40,0,F8-8))
Y8Y8=MIN(SUM(AG8:AK8),40)
Z8Z8=IF(Y8<=40,0,Y8-40)
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
myall_blues said:
Try these formulas. Note they've all changed (columns F:AA).

Book1
FGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
5MondayTuesdayWednesdayThursdayFridaySaturdaySundayWEEKLY TOTALS
6       
7STOTDTSTOTDTSTOTDTSTOTDTSTOTDTSTOTSTOTSTOTDTNONameTradeIDJobMondayTuesdayWednesdayThursdayFridaySaturdaySunday
8608090608000003701JoeSuper1268968
Sheet1
Cell Formulas
RangeFormula
F6F6=IF($M$2="","",$M$2-6)
I6I6=IF($M$2="","",$M$2-5)
L6L6=IF($M$2="","",$M$2-4)
O6O6=IF($M$2="","",$M$2-3)
R6R6=IF($M$2="","",$M$2-2)
U6U6=IF($M$2="","",$M$2-1)
W6W6=IF($M$2="","",$M$2-0)
F8,U8,W8,R8,O8,L8,I8F8=LET(hours,XLOOKUP(F$5,$AG$7:$AM$7,$AG8:$AM8),IF(hours<=40,hours,MIN(8,hours)))
G8,V8,X8,S8,P8,M8,J8G8=LET(hours,XLOOKUP(F$5,$AG$7:$AM$7,$AG8:$AM8),IF(hours<=40,0,F8-8))
Y8Y8=MIN(SUM(AG8:AK8),40)
Z8Z8=IF(Y8<=40,0,Y8-40)
Click to expand...
Ok thanks for reply. I am going to try it and work on sheet later. I will let you know 👍
 
Upvote 0
Hi i started working on this again and used above formulas. Works fine but when i go over 40 hours like below. It should be now 40 and 2 OT.

Which Wednesday would be 10 and 2 not 12. Almost there. If it goes over 40 i need to start breaking out OT. If it under 40 then its good the way it is.


Test.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
5MondayTuesdayWednesdayThursdayFridaySaturdaySundayWEEKLY TOTALS
67/227/237/247/257/267/277/28
7No.Local\TradeIDJOBSTOTDTSTOTDTSTOTDTSTOTDTSTOTDTSTOTSTOTSTOTDTNONameTradeIDJobMondayTuesdayWednesdayThursdayFridaySaturdaySunday
81JoeSuper5125 5th Street8.00.08.00.012.00.06.00.08.00.00.00.00.00.040.0-1JoeSuper120881268
Data
Cell Formulas
RangeFormula
F6F6=IF($M$2="","",$M$2-6)
I6I6=IF($M$2="","",$M$2-5)
L6L6=IF($M$2="","",$M$2-4)
O6O6=IF($M$2="","",$M$2-3)
R6R6=IF($M$2="","",$M$2-2)
U6U6=IF($M$2="","",$M$2-1)
W6W6=IF($M$2="","",$M$2-0)
F8,U8,W8,R8,O8,L8,I8F8=LET(hours,XLOOKUP(F$5,$AG$7:$AM$7,$AG8:$AM8),IF(hours<=40,hours,MIN(8,hours)))
G8,V8,X8,S8,P8,M8,J8G8=LET(hours,XLOOKUP(F$5,$AG$7:$AM$7,$AG8:$AM8),IF(hours<=40,0,F8-8))
Y8Y8=MIN(SUM(AG8:AK8),40)
Z8Z8=IF(Y8<=40,0,Y8-40)
AF8AF8=Data!AF14
 
Upvote 0
Try this then.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
5MondayTuesdayWednesdayThursdayFridaySaturdaySundayWEEKLY TOTALS
6       
7No.Local\TradeIDJOBSTOTDTSTOTDTSTOTDTSTOTDTSTOTDTSTOTSTOTSTOTDTNONameTradeIDJobMondayTuesdayWednesdayThursdayFridaySaturdaySunday
81JoeSuper5125 5th Street8080102608000004021JoeSuper12=Data!AF14881268
Sheet2
Cell Formulas
RangeFormula
F6F6=IF($M$2="","",$M$2-6)
I6I6=IF($M$2="","",$M$2-5)
L6L6=IF($M$2="","",$M$2-4)
O6O6=IF($M$2="","",$M$2-3)
R6R6=IF($M$2="","",$M$2-2)
U6U6=IF($M$2="","",$M$2-1)
W6W6=IF($M$2="","",$M$2-0)
F8,U8,W8,R8,O8,L8,I8F8=LET(hours,SUM($AG8:$AM8),_daily,XLOOKUP(F$5,$AG$7:$AM$7,$AG8:$AM8),IF(OR(hours<=40,_daily<=8),_daily,_daily-MOD(hours,40)))
G8,V8,X8,S8,P8,M8,J8G8=LET(hours,SUM($AG8:$AM8),_daily,XLOOKUP(F$5,$AG$7:$AM$7,$AG8:$AM8),IF(OR(hours<=40,_daily<=8),0,F8-8))
Y8Y8=SUMIF($F7:$X7,"ST",$F8:$X8)
Z8Z8=SUMIF($F7:$X7,"OT",$F8:$X8)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Spreadsheet to calculate overtime and total hours
Replies
1
Views
965
HyraxWithAFlamethrower
H
W
  • Question Question
Task Management Calc
Replies
1
Views
176
Woejeber
W
Z
  • Question Question
Fix timesheet totals for regular time and overtime, Maybe Macro
2
Replies
18
Views
832
Bosquedeguate
Bosquedeguate
J
  • Question Question
Daily summary worksheet that extracts data from up to 40 worksheets and emails the sheet as an excel attachment.
Replies
4
Views
539
juscuz419
J
D
  • Question Question
Trying to force people to not use a comma (even when pasting) or enter a number in a specific format
2 3
Replies
22
Views
753
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,155
Messages
6,170,403
Members
452,325
Latest member
BlahQz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top