EXCEL MONTHLY TIMESHEET NIGHTMARE

cuddlezuk

Board Regular
Joined
Aug 29, 2020
Messages
58
Office Version
  1. 2019
Platform
  1. Windows
I've been trying to make a timesheet for my work, so my overtime gets paid to me.

Looking for a monthly timesheet starting 20th of every month to the 19th.

Monday to Thursday, but paid in 15 minute intervals, so if I clock in at 0520, I get paid from 0530, if I clock out at 1720, I get paid to 1715. as the round it back if before clock out early, and round it forward if I clock in early.

So a time sheet, with Day, Date, Start Time, 30 Min Break deduction, Finish Time, Showing my 10 hrs normal time with the break, anything over is overtime, so 10hrs 30mins normal, as they deduct the break,

At the bottom, total hours worked, plus total overtime at the bottom?

I can't get it myself, tried, tried and tried, without any success.
Can you get 24hr clock displayed, so start time 0520 and 1730 end time ?

Any chance you can throw one together, so I can tweak it if needed?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Any excel posts are meant to be here, not in message or external source
 
Upvote 0
Hi Cuddlezuk,

This should work as long as none of your shifts go over midnight. Don't forget to format the Totals as Custom [h]:mm so it treats it as duration and not time of day.

You can copy and paste the month to the right for September, October, etc.

The pale yellow cells are those which you enter.

Cuddlezuk.xlsx
ABCDEFGHIJ
120-Aug-20
2DayDateStart TimeFinish TimeBreakRounded Start TimeRounded Finish TimeWorked Less BreakStandardOvertime
3Thu20-Aug-205:2017:200:305:3017:1511:1510:001:15
4Fri21-Aug-205:1617:200:305:3017:1511:1510:001:15
5Sat22-Aug-20      
6Sun23-Aug-20      
7Mon24-Aug-205:2017:200:305:3017:1511:1510:001:15
8Tue25-Aug-205:2017:200:305:3017:1511:1510:001:15
9Wed26-Aug-205:2017:200:305:3017:1511:1510:001:15
10Thu27-Aug-205:2017:200:305:3017:1511:1510:001:15
11Fri28-Aug-205:2017:200:305:3017:1511:1510:001:15
12Sat29-Aug-20      
13Sun30-Aug-20      
14Mon31-Aug-205:2017:200:305:3017:1511:1510:001:15
15Tue01-Sep-205:2017:200:305:3017:1511:1510:001:15
16Wed02-Sep-205:2017:200:305:3017:1511:1510:001:15
17Thu03-Sep-205:2017:200:305:3017:1511:1510:001:15
18Fri04-Sep-205:2017:200:305:3017:1511:1510:001:15
19Sat05-Sep-205:2017:200:305:3017:1511:1510:001:15
20Sun06-Sep-205:2017:200:305:3017:1511:1510:001:15
21Mon07-Sep-205:2017:200:305:3017:1511:1510:001:15
22Tue08-Sep-205:2017:200:305:3017:1511:1510:001:15
23Wed09-Sep-205:2017:200:305:3017:1511:1510:001:15
24Thu10-Sep-205:2017:200:305:3017:1511:1510:001:15
25Fri11-Sep-205:2017:200:305:3017:1511:1510:001:15
26Sat12-Sep-205:2017:200:305:3017:1511:1510:001:15
27Sun13-Sep-205:2017:200:305:3017:1511:1510:001:15
28Mon14-Sep-205:2017:200:305:3017:1511:1510:001:15
29Tue15-Sep-205:2017:200:305:3017:1511:1510:001:15
30Wed16-Sep-205:2017:200:305:3017:1511:1510:001:15
31Thu17-Sep-205:2017:200:305:3017:1511:1510:001:15
32Fri18-Sep-205:2017:200:305:3017:1511:1510:001:15
33Sat19-Sep-205:2017:200:305:3017:1511:1510:001:15
34        
35        
36Totals303:45270:0033:45
Sheet1
Cell Formulas
RangeFormula
A3:A35A3=TEXT(B3,"ddd")
B3B3=B1
B4:B35B4=IF(B3="","",IF(B3+1=EDATE(B$1,1),"",B3+1))
E3:E35E3=IF(C3<>"",TIME(0,30,0),"")
F3:F35F3=IF(C3<>"",CEILING(C3,"0:15"),"")
G3:G35G3=IF(D3<>"",FLOOR(D3,"0:15"),"")
H3:H35H3=IF(G3<>"",G3-F3-E3,"")
I3:I35I3=IF(H3="","",IF(H3>TIME(10,0,0),TIME(10,0,0),H3))
J3:J35J3=IF(I3="","",IF(H3>TIME(10,0,0),H3-TIME(10,0,0),""))
[/
Cell Formulas
RangeFormula
Hi, slight problem,my work has made start time 0545 and 45 min break gets taken off me so now I have to work 10hrs 45mins before any overtime. Don't get paid if I arrive early. Finish Time 1630 is there any way of editing it
 
Upvote 0
I've got the 45 min breaks sorted, just 0545 start time so if I clock in any time before it, I still start at 0545, but if I,m late, 0546 is 0600, as it rounds backwards at start time to every 15 minutes, 0601 rounds to 0615, 0555 rounds to 0600 etc
 
Upvote 0
Hi Cuddlezuk,

You've quoted the first version I sent but we agreed several changes (like subtracting O/T hours if you worked short days) so I'm not sure where we ended up. I think this was the latest version and you'd need to change two columns:

Column E for Break would need to change to 45 minutes, so:
=IF(C3<>"",TIME(0,45,0),"")

Column F would need to check for earlier than 5:45 start and if so then force a 5:45 start:
=IF(C3<>"",IF(C3<TIME(5,45,0),TIME(5,45,0),CEILING(C3,"0:15")),"")

Copy those E3 and F3 down to E45:F45

Here's the XL2BB version:

Cuddlezuk.xlsx
ABCDEFGHIJK
120-Jul-20
2DayDateStart TimeFinish TimeBreakRounded Start TimeRounded Finish TimeWorked Less BreakStandardWorked Overtime-Overtime
3Mon20-Jul-205:4516:450:455:4516:4510:1510:000:15 
4Tue21-Jul-206:0017:150:456:0017:1510:3010:000:30 
5Wed22-Jul-206:0017:000:456:0017:0010:1510:000:15 
6Thu23-Jul-206:0016:000:456:0016:009:159:15 0:45
7Fri24-Jul-20       
8Sat25-Jul-20       
9Sun26-Jul-20       
10Mon27-Jul-206:0017:450:456:0017:4511:0010:001:00 
11Tue28-Jul-206:0017:300:456:0017:3010:4510:000:45 
12Wed29-Jul-206:0017:150:456:0017:1510:3010:000:30 
13Thu30-Jul-206:0016:300:456:0016:309:459:45 0:15
14Fri31-Jul-20       
15Sat01-Aug-20       
16Sun02-Aug-20       
17Mon03-Aug-206:0017:300:456:0017:3010:4510:000:45 
18Tue04-Aug-206:0017:300:456:0017:3010:4510:000:45 
19Wed05-Aug-206:0017:000:456:0017:0010:1510:000:15 
20Thu06-Aug-206:0016:150:456:0016:159:309:30 0:30
21Fri07-Aug-20       
22Sat08-Aug-20       
23Sun09-Aug-20       
24Mon10-Aug-206:0016:450:456:0016:4510:0010:00  
25Tue11-Aug-206:0016:450:456:0016:4510:0010:00  
26Wed12-Aug-206:0017:150:456:0017:1510:3010:000:30 
27Thu13-Aug-206:0016:300:456:0016:309:459:45 0:15
28Fri14-Aug-20       
29Sat15-Aug-20       
30Sun16-Aug-20       
31Mon17-Aug-206:0016:450:456:0016:4510:0010:00  
32Tue18-Aug-206:0017:000:456:0017:0010:1510:000:15 
33Wed19-Aug-206:0018:150:456:0018:1511:3010:001:30 
34         
35         
36Totals195:30188:157:151:45
37Paid O/T5:30
Jan2021
Cell Formulas
RangeFormula
A3:A35A3=TEXT(B3,"ddd")
B3B3=B1
B4:B35B4=IF(B3="","",IF(B3+1=EDATE(B$1,1),"",B3+1))
E3:E35E3=IF(C3<>"",TIME(0,45,0),"")
F3:F35F3=IF(C3<>"",IF(C3<TIME(5,45,0),TIME(5,45,0),CEILING(C3,"0:15")),"")
G3:G35G3=IF(D3<>"",FLOOR(D3,"0:15"),"")
H3:H35H3=IF(G3<>"",G3-F3-E3,"")
I3:I35I3=IF(H3="","",IF(H3>TIME(10,0,0),TIME(10,0,0),H3))
H36:K36H36=SUM(H3:H35)
K2K2="-Overtime"
K3:K35K3=IF(OR(I3="",I3=TIME(10,0,0)),"",IF(I3<10,TIME(10,0,0)-I3,""))
J3:J35J3=IF(I3="","",IF(H3>TIME(10,0,0),H3-TIME(10,0,0),""))
J37J37=J36-K36
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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