# EXCEL MONTHLY TIMESHEET NIGHTMARE

#### cuddlezuk

##### Board Regular
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?

##### New Member
You just winding me up
i got a little busy but am still here though admin thought i was posting a

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### cuddlezuk

##### Board Regular
Any excel posts are meant to be here, not in message or external source

##### New Member
timecard file
check out that one, maybe you want the same formulas. if it works. just say thank you

#### cuddlezuk

##### Board Regular
It's nothing like what I was looking for, sorry.

#### cuddlezuk

sorry

##### New Member
It's nothing like what I was looking for, sorry.
I ve never heard of it, can you postba copy here?

Its in this chat

#### cuddlezuk

##### Board Regular
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

#### cuddlezuk

##### Board Regular
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

##### Well-known Member
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

Replies
5
Views
60
Replies
1
Views
388
Replies
32
Views
1K
Replies
0
Views
405
Replies
0
Views
257

1,147,568
Messages
5,741,882
Members
423,693
Latest member
Excelquestion35

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

### Which adblocker are you using?

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

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