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?

 

[Logit]

.
Excel Timesheet Calculator Template for 2020 [FREE DOWNLOAD]

 

[cuddlezuk]

Tried that and not any good

 

[Toadstool]

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),""))
H36:J36	H36	=SUM(H3:H35)

 

[cuddlezuk]

How do I download it, as it looks perfect

 

[Toadstool]

How do I download it, as it looks perfect

Click the pages symbol and it should copy to your clipboard.
Now paste into A1 of an empty sheet and it will populate the text and formulas.
You'll need to reformat as it doesn't paste column widths, fonts, etc.

 

[cuddlezuk]

Excel 2019 doesn't show paste

 

[Toadstool]

Left-click into A1
Hold down Ctrl and tap V

 

[cuddlezuk]

Looks like this

 

[Toadstool]

My PC is from the USA and I've kept the region settings as mm/dd/yyyy but for the UK you'll need to enter the starting date in B1 as dd/mm/yyyy so type in 20/8/2020