EXCEL MONTHLY TIMESHEET NIGHTMARE

[cuddlezuk]

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?

 

[padapinto]

You just winding me up

i got a little busy but am still here though admin thought i was posting a
link

 

[cuddlezuk]

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

 

[padapinto]

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

 

[cuddlezuk]

It's nothing like what I was looking for, sorry.
Toadstool's timesheet is perfect.

 

[cuddlezuk]

sorry

 

[padapinto]

It's nothing like what I was looking for, sorry.
Toadstool's timesheet is perfect.

I ve never heard of it, can you postba copy here?

 

[cuddlezuk]

Its in this chat

 

[cuddlezuk]

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
	A	B	C	D	E	F	G	H	I	J
1		20-Aug-20
2	Day	Date	Start Time	Finish Time	Break	Rounded Start Time	Rounded Finish Time	Worked Less Break	Standard	Overtime
3	Thu	20-Aug-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
4	Fri	21-Aug-20	5:16	17:20	0:30	5:30	17:15	11:15	10:00	1:15
5	Sat	22-Aug-20
6	Sun	23-Aug-20
7	Mon	24-Aug-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
8	Tue	25-Aug-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
9	Wed	26-Aug-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
10	Thu	27-Aug-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
11	Fri	28-Aug-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
12	Sat	29-Aug-20
13	Sun	30-Aug-20
14	Mon	31-Aug-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
15	Tue	01-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
16	Wed	02-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
17	Thu	03-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
18	Fri	04-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
19	Sat	05-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
20	Sun	06-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
21	Mon	07-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
22	Tue	08-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
23	Wed	09-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
24	Thu	10-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
25	Fri	11-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
26	Sat	12-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
27	Sun	13-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
28	Mon	14-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
29	Tue	15-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
30	Wed	16-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
31	Thu	17-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
32	Fri	18-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
33	Sat	19-Sep-20	5:20	17:20	0:30	5:30	17:15	11:15	10:00	1:15
34
35
36							Totals	303:45	270:00	33:45
Sheet1

Cell Formulas
Range	Formula
A3:A35	A3	=TEXT(B3,"ddd")
B3	B3	=B1
B4:B35	B4	=IF(B3="","",IF(B3+1=EDATE(B$1,1),"",B3+1))
E3:E35	E3	=IF(C3<>"",TIME(0,30,0),"")
F3:F35	F3	=IF(C3<>"",CEILING(C3,"0:15"),"")
G3:G35	G3	=IF(D3<>"",FLOOR(D3,"0:15"),"")
H3:H35	H3	=IF(G3<>"",G3-F3-E3,"")
I3:I35	I3	=IF(H3="","",IF(H3>TIME(10,0,0),TIME(10,0,0),H3))
J3:J35	J3	=IF(I3="","",IF(H3>TIME(10,0,0),H3-TIME(10,0,0),""))

[/

Cell Formulas
Range	Formula

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]

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

 

[Toadstool]

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
	A	B	C	D	E	F	G	H	I	J	K
1		20-Jul-20
2	Day	Date	Start Time	Finish Time	Break	Rounded Start Time	Rounded Finish Time	Worked Less Break	Standard	Worked Overtime	-Overtime
3	Mon	20-Jul-20	5:45	16:45	0:45	5:45	16:45	10:15	10:00	0:15
4	Tue	21-Jul-20	6:00	17:15	0:45	6:00	17:15	10:30	10:00	0:30
5	Wed	22-Jul-20	6:00	17:00	0:45	6:00	17:00	10:15	10:00	0:15
6	Thu	23-Jul-20	6:00	16:00	0:45	6:00	16:00	9:15	9:15		0:45
7	Fri	24-Jul-20
8	Sat	25-Jul-20
9	Sun	26-Jul-20
10	Mon	27-Jul-20	6:00	17:45	0:45	6:00	17:45	11:00	10:00	1:00
11	Tue	28-Jul-20	6:00	17:30	0:45	6:00	17:30	10:45	10:00	0:45
12	Wed	29-Jul-20	6:00	17:15	0:45	6:00	17:15	10:30	10:00	0:30
13	Thu	30-Jul-20	6:00	16:30	0:45	6:00	16:30	9:45	9:45		0:15
14	Fri	31-Jul-20
15	Sat	01-Aug-20
16	Sun	02-Aug-20
17	Mon	03-Aug-20	6:00	17:30	0:45	6:00	17:30	10:45	10:00	0:45
18	Tue	04-Aug-20	6:00	17:30	0:45	6:00	17:30	10:45	10:00	0:45
19	Wed	05-Aug-20	6:00	17:00	0:45	6:00	17:00	10:15	10:00	0:15
20	Thu	06-Aug-20	6:00	16:15	0:45	6:00	16:15	9:30	9:30		0:30
21	Fri	07-Aug-20
22	Sat	08-Aug-20
23	Sun	09-Aug-20
24	Mon	10-Aug-20	6:00	16:45	0:45	6:00	16:45	10:00	10:00
25	Tue	11-Aug-20	6:00	16:45	0:45	6:00	16:45	10:00	10:00
26	Wed	12-Aug-20	6:00	17:15	0:45	6:00	17:15	10:30	10:00	0:30
27	Thu	13-Aug-20	6:00	16:30	0:45	6:00	16:30	9:45	9:45		0:15
28	Fri	14-Aug-20
29	Sat	15-Aug-20
30	Sun	16-Aug-20
31	Mon	17-Aug-20	6:00	16:45	0:45	6:00	16:45	10:00	10:00
32	Tue	18-Aug-20	6:00	17:00	0:45	6:00	17:00	10:15	10:00	0:15
33	Wed	19-Aug-20	6:00	18:15	0:45	6:00	18:15	11:30	10:00	1:30
34
35
36							Totals	195:30	188:15	7:15	1:45
37									Paid O/T	5:30
Jan2021

Cell Formulas
Range	Formula
A3:A35	A3	=TEXT(B3,"ddd")
B3	B3	=B1
B4:B35	B4	=IF(B3="","",IF(B3+1=EDATE(B$1,1),"",B3+1))
E3:E35	E3	=IF(C3<>"",TIME(0,45,0),"")
F3:F35	F3	=IF(C3<>"",IF(C3<TIME(5,45,0),TIME(5,45,0),CEILING(C3,"0:15")),"")
G3:G35	G3	=IF(D3<>"",FLOOR(D3,"0:15"),"")
H3:H35	H3	=IF(G3<>"",G3-F3-E3,"")
I3:I35	I3	=IF(H3="","",IF(H3>TIME(10,0,0),TIME(10,0,0),H3))
H36:K36	H36	=SUM(H3:H35)
K2	K2	="-Overtime"
K3:K35	K3	=IF(OR(I3="",I3=TIME(10,0,0)),"",IF(I3<10,TIME(10,0,0)-I3,""))
J3:J35	J3	=IF(I3="","",IF(H3>TIME(10,0,0),H3-TIME(10,0,0),""))
J37	J37	=J36-K36