Payslips

[HappyChappy]

No idea how to.... i want to check my payslip but its a bit complicated. work 50hrs per week over 5 days driving but if vehicle is broken down no bonus earned
hourly Rate £15.50 upto 39 hours, + 30 mins for servicing per day. so 5 days of 7am till 5pm works out at 41.50 at basic pay
Overtime would be 4:75 hrs per day paid at 23.25per hour
bonus of 1.40 per hour if productive, minus servicing hours of 30mins per day. so 43:75hrs bonus for week if vehicle is used with no down time for breakdowns
mileage of 0.17 per mile 45min break unpaid.
can any guru's out there give me an idea on how this can be achieved via a spreadsheet without macro's at it needs to run on a phone as well as a computer.

 

[HappyChappy]

g5 was a botch to get the unpaid break from the overtime figure my sheet has many basic flaws that's why i need help i was making things worse the more i tried lol

 

[HappyChappy]

i basically want to be able to enter 07:00 start and 17:00 finish and on a daily basic have it show me how much ive earned for the day then total up the 5 days or however many it is to hopefully match the payslip.

 

[HappyChappy]

yes the basic concept is fine 39 basic hours would be paid at 15.50 per hour.
30 mins per day for checking vehicle paid at basic rate total of 2.50hrs per week so 41.50 total hours worked at basic rate.
overtime for any hours over the 41.50 less the 45mins per day lunch break. so a standard week with no breakdowns would be 46.25 total

 

[etaf]

the lunch break is not included in the standard hours then -

overtime for any hours over the 41.50 less the 45mins per day lunch break

and why the 15:18 (which i guess is to work out a 41.5 hour week / 5 - 07:00 to 15:18 is a 41.5 hour week )

and the 15:45 ???
you dont count breaks or lunch in the basic hours

so if you entered
07:00 and 15:18 - that would be a standard week of 41.5 and NO breaks - paid at £15.50

then you have column F calculating to 15:45 - and that pays a bonus
really sorry , it must be me, i'm just not following

g5 was a botch to get the unpaid break from the overtime figure my sheet has many basic flaws that's why i need help i was making things worse the more i tried lol

Maybe we can work ou thow to modify your sheet , as it seems so close to what you want

is the times and bonus and rates all working OK - with the exception of the breakdown part

 

[HappyChappy]

this is a weekly payslip. 39 hrs basic pay + 2.25hrs for servicing vehicle .30 per day. bonus is paid for hours - 45mins for breaks - .30 for servicing. overtime is paid for hours over 41.50 - breaks hope this makes things clear I'm getting myself in a right muddle trying to work it out if you have any breakdowns any hours comes off the bonus also

 

[Logit]

Continue on in the same format as you have displayed in your last post #15. You are doing fine and will eventually generate your solution.

 

[HappyChappy]

Payslip.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Day	Date	Start Time	Finish Time	Rounded Time to nearest 15 Min	Rounded Time to nearest 15 Min	Hours worked	Total_45 Break	Basic Hours	Basic + Servicing	Basic Hours + Breaks	Overtime	Paid at Std Rate		Servicing	break	Hours @ Overtime	Total Duty Time		Bonus	over time	Fuel Mileage	Pence Per Mile	Breakdowns		Daily Total
2			Days Hours
3	Monday	05/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	8:00	8:30	8:45	0:57	131.75		0:30	0:45	0:45	10:00	1:15	£12.25	22.09	20.00	3.47	0.08		169.56
4	Tuesday	06/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	8:00	8:30	8:45	0:57	131.75		0:30	0:45	0:45	10:00	1:15	£12.25	22.09	20.00	3.47			169.56
5	Wednesday	07/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	8:00	8:30	8:45	0:57	131.75		0:30	0:45	0:45	10:00	1:15	£12.25	22.09	20.00	3.47			169.56
6	Thursday	08/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	8:00	8:30	8:45	0:57	131.75		0:30	0:45	0:45	10:00	1:15	£12.25	22.09	20.00	3.47			169.56
7	Friday	09/02/2024	07:00	17:00	07:00	17:00	10:00	9:15	7:00	7:30	8:45	0:57	116.25	0.04	0:30	0:45	0:45	10:00	2:15	£12.25	22.09	20.00	3.47			154.06
8	Saturday	10/02/2024			00:00	00:00	0:00		0:00	0:00	0:00	0:00	0.00			0:00	0:00	0:00	0:00	£0.00	0.00		0.00			0.00
9	Sunday	11/02/2024			00:00	00:00	0:00		0:00	0:00	0:00	0:00	0.00			0:00	0:00	0:00	0:00	£0.00	0.00		0.00			0.00
10									15:00	17:30	19:45	4:45			1.25		3:45
11									39.0	41.30	43.45	4.45	643.25				3.45			61.25	110.44		17.35		Gross Weekly Total	832.29
12	Break	0:45:00
13	£15.50	STANDARD
14	£23.25	O/Time
15	£1.40	Bonus
16	£0.17	Fuel
050224 (2)

Cell Formulas
Range	Formula
E3:F9	E3	=MROUND(C3,(1/24/60)*15)
G3:G9	G3	=MEDIAN("07:00","17:00",IF(F3<E3,1+F3,F3))-MEDIAN("07:00","17:00",E3)
H3:H7	H3	=G3-B$12
I3:I6,I8:I9	I3	=G3-O3-P3-Q3
J3:J9	J3	=I3+O3
K3:K6,K8:K9	K3	=I3+P3
L3:L9	L3	=MEDIAN("16:03","17:00",IF(F3<E3,1+F3,F3))-MEDIAN("16:03","17:00",E3)
M3:M9	M3	=(J3*24)*A$13
I7	I7	=G7-O7-P7-Q7-N7
K7	K7	=I7+P7+O7+O7
R3:R9	R3	=H3+Q3
S3:S9	S3	=H3-I3
T3:T9	T3	=(K3*24)*A$15
U3:U9	U3	=(L3*24)*A$14
O3:O7	O3	=O$10/60
B4:B9	B4	=B3+1
P3:P9	P3	=G3-H3
W3:W9	W3	=V3*A$16
Z3:Z9	Z3	=M3+T3+U3+W3
I10:L10,Q10	I10	=SUM(I3:I9)
I11:L11,Q11	I11	=CONCATENATE(24*DAY(I10)+HOUR(I10),".",MINUTE(I10))
M11,Z11,W11,T11:U11	M11	=SUM(M3:M9)
Q3:Q9	Q3	=MOD(F3-E3,1)-H3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:F9	Cell Value	>0	text	NO

Getting there but col L is right result but wrong formula
col N is a fudge to get the basic hours in col I right
and have no idea how to deduct from bonus for standing time

 

[Logit]

and have no idea how to deduct from bonus for standing time

Well, you previously stated :

bonus of 1.40 per hour if productive, minus servicing hours of 30mins per day.