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?

 

[cuddlezuk]

Done it, all complete thanks!!

 

[cuddlezuk]

Finished! Thx!!

 

[Toadstool]

clocking in, if I clock in at 0505 that counts as 0500, if I clock out at 1725, that counts as 1715, don't know if it' possible to calculate that.
or if I start 0440, thats 0430 paid from, clocking out 1658 would be 1645

I see the final message saying it's working but I'm a little confused by this message.
I assumed if you missed 05:00 then any clocking 05:01 through 05:14 would be treated as an 05:15 start. Is that not correct?

 

[cuddlezuk]

I see the final message saying it's working but I'm a little confused by this message.
I assumed if you missed 05:00 then any clocking 05:01 through 05:14 would be treated as an 05:15 start. Is that not correct?

My work is tight, if you work to 1729 they pay you to 1715 as you never reached 1730. Clocking in at 0529 gets rounded back to 0515,

 

[Toadstool]

My work is tight, if you work to 1729 they pay you to 1715 as you never reached 1730. Clocking in at 0529 gets rounded back to 0515,

Ah! Then I had it right. Good.

 

[cuddlezuk]

Yes, my apologies, all the calculations went to my head. My work has been mispaying me for months, this way, if my overtime only goes thru as 8hrs I'll take next Monday off.

 

[cuddlezuk]

Gets rounded forward clocking in, rounded back clocking out.

 

[cuddlezuk]

Thanks for your help, I would never of got it. If I did the sum would be huge...

 

[cuddlezuk]

Hi Again, Is there any way to show if I worked less that 10hrs less 30 min brake as a -overtime, so it takes that off the total?

 

[Toadstool]

Hi Cuddlezuk,

The sheet Totals already account for any short days. Let's assume you worked every weekday in 20 August to 19 September, 22 weekdays, then the Total for Standard Hours would be 10 x 22 = 220. If on that last work day, 18 September, you only did a rounded 9 hours then this would show Standard hours of 08:30 (9 hours less thirty minutes). The Standard Total wouldn't show 220 hours but 218:30 hours/minutes.

You can see I've added a column "-Overtime" but I really don't see what it achieves. It would better be called "Missed Opportunity" to achieve a full months scheduled hours.

If you want it then here's the XL2BB version:

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

Cell Formulas
Range	Formula
K2	K2	="-Overtime"
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),""))
K3:K35	K3	=IF(OR(I3="",I3=TIME(10,0,0)),"",IF(I3<10,TIME(10,0,0)-I3,""))
H36:K36	H36	=SUM(H3:H35)