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]

I'm sorry about this, you dont need to help us out, probably I'm sounding like a pain by now.

 

[Toadstool]

Hi Cuddlezuk,

I'd be surprised if they work on a monthly basis but if they do then your "-Overtime" works.

Here's an example with 5 hours O/T worked at the start of the month and 2 days showing short hours of 1:45 at the end of the month, so it nets to 3:15 payable.

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	Worked Overtime	-Overtime
3	Thu	20-Aug-20	5:25	18:04	0:30	5:30	18:00	12:00	10:00	2:00
4	Fri	21-Aug-20
5	Sat	22-Aug-20
6	Sun	23-Aug-20
7	Mon	24-Aug-20	6:00	18:00	0:30	6:00	18:00	11:30	10:00	1:30
8	Tue	25-Aug-20	6:00	18:00	0:30	6:00	18:00	11:30	10:00	1:30
9	Wed	26-Aug-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
10	Thu	27-Aug-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
11	Fri	28-Aug-20
12	Sat	29-Aug-20
13	Sun	30-Aug-20
14	Mon	31-Aug-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
15	Tue	01-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
16	Wed	02-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
17	Thu	03-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
18	Fri	04-Sep-20
19	Sat	05-Sep-20
20	Sun	06-Sep-20
21	Mon	07-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
22	Tue	08-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
23	Wed	09-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
24	Thu	10-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
25	Fri	11-Sep-20
26	Sat	12-Sep-20
27	Sun	13-Sep-20
28	Mon	14-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
29	Tue	15-Sep-20	6:00	16:30	0:30	6:00	16:30	10:00	10:00
30	Wed	16-Sep-20	6:30	16:15	0:30	6:30	16:15	9:15	9:15		0:45
31	Thu	17-Sep-20	7:00	16:30	0:30	7:00	16:30	9:00	9:00		1:00
32	Fri	18-Sep-20
33	Sat	19-Sep-20
34
35
36							Totals	173:15	168:15	5:00	1:45
37									Paid O/T	3:15
Sheet1 (4)

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))
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

 

[cuddlezuk]

Is it possible to show -0:45 for finishing early, or name column something else hours short or something like that

 

[Toadstool]

I can't show negative duration (without involving a DeLorean) so yes, just type into cell K2 with Hours Short.

 

[cuddlezuk]

I thought that might be a problem, or Finished Early.

But, thanks for your time in helping me out, it's much appreciated.

 

[cuddlezuk]

Thanks, working

 

[cuddlezuk]

What format should it be saved in?
.ods or .xlsx

 

[Toadstool]

It seems to work in either as I saved as ODS and opened in Excel OK.

 

[cuddlezuk]

Whats the correct file format?

 

[Toadstool]

There's no correct format as it depends on what software you're using and what functions are available in that software.
I used Excel 2016 and saved as xlsx