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.

 

[etaf]

are you entering the hours worked each week , mileage and breaks taken
and adding in the overtime perweek - OR are you just entering every day the start and stop times and also the

should be straight forward to work out - JUST depends on what you are entering and what the rules are

how often do you get a payslip - and if you are checking that - then i guess the data in the spreadsheet - needs to match a payslip

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

so you enter
Hours worked Normal
Hours worked overtime

Miles - is this for the week, or odometer reading at start and end
breaks
etc

i just need a little more clarity on how you want to enter data and how seen

 

[HappyChappy]

i want to enter start time 07:00 end time 17:00 and mileage the rest is calculated automatically on a daily basis
i was able to make a spreadsheet but when i have a short day or breakdown for say a few hours that is way beyond my capabilities
I get a weekly payslip

 

[HappyChappy]

example of standard week payslip

 

[etaf]

i want to enter start time 07:00 end time 17:00 and mileage the rest is calculated automatically on a daily basis

so you do that every day for the week

just perhaps need to understand the productive bit and service time

bonus of 1.40 per hour if productive,

minus servicing hours of 30mins per day.

so if you enter 07:00 to 17:00 = 10hours - so does that add another 30mins = 10.5 hours so 10.5 * 5 = 52,5 hour
or just 50hours and the 30mins service time is not taken into account

Basic pay is 41.5 hours in the image - how is that worked OUT
and the non-tax part at £1 an hour 17.5 - whats that - some sort of mileage ????

Not exactly following that

maybe we can see the spreadsheet you have been using and modify it to work

it should be straight forward - depending on data entry - and also , my understanding - sorry about that - trying to follow as best i can

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[HappyChappy]

Payslip.xlsx
	U
17
050224

the service is 30mins for vehicle checks it is not added to make 52.5 but it is added to the basic 39 hours pay to bring that upto 41.5 at basic rate any hours over that are paid at 1.5 rate less the 45min unpaid break

 

[HappyChappy]

Payslip.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Day	Date	Start Time	Finish Time	Basic Hours		Break Unpaid	Paid at Std Rate	Hours @ Overtime		Rounded Time to nearest 15 Min	Rounded Time to nearest 15 Min	Overtime Total	Bonus	Fuel Mileage	Pence Per Mile	Adjustments		Daily Total	Notes/Comment
2			Days Hours
3	Monday	05/02/2024	07:00	17:00	8:18	8:45	0:38	128.65	1:42	0:57	7:00	17:00	£22.09	12.25	20.00	3.47			166.46
4	Tuesday	06/02/2024	07:00	17:00	8:18	8:45	0:38	128.65	1:42	0:57	7:00	17:00	£22.09	12.25	20.00	3.47			166.46
5	Wednesday	07/02/2024	07:00	15:45	8:18	8:45	0:10	128.65	0:27	0:15	7:00	15:45	£5.85	12.25	20.00	3.47			150.22
6	Thursday	08/02/2024	07:00	15:45	8:18	8:45	0:10	128.65	0:27	0:15	7:00	15:45	£5.85	12.25	20.00	3.47			150.22	Broken Down
7	Friday	09/02/2024	07:00	15:45	8:18	8:45	0:10	128.65	0:27	0:15	7:00	15:45	£5.85	12.25	20.00	3.47	17.18		150.22	Broken Down
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	Broken Down
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
10								643.25	41:8			17:30	61.71	61.25		17.35	17.18	Gross Weekly Total	766.38
11
12
13	£15.50	STANDARD							#REF!			1.73
14	£23.25	O/Time
15	£1.40	Bonus
16	£0.17	Fuel
17
18																		Tax/NI/Pension	177.69
19																		Net Weekly Total	588.70
050224

Cell Formulas
Range	Formula
E3:E9	E3	=MEDIAN("07:00","15:18",IF(L3<K3,1+L3,L3))-MEDIAN("07:00","15:18",K3)
F3:F9	F3	=MEDIAN("07:00","15:45",IF(L3<K3,1+L3,L3))-MEDIAN("07:00","15:45",K3)
G3:G9	G3	=I3/2.62
H3:H9	H3	=(E3*24)*A$13
I3:I9	I3	=MOD(L3-K3,1)-E3
J3:J9	J3	=I3/1.7895
K3:L9	K3	=MROUND(C3,(1/24/60)*15)
M3:M9	M3	=(J3*24)*A$14
N3:N9	N3	=(F3*24)*A$15
L10	L10	=SUM(E3:E9)
M10:N10,P10:Q10,H10	M10	=SUM(M3:M9)
B4:B9	B4	=B3+1
P3:P9	P3	=O3*A$16
I10	I10	=CONCATENATE(24*DAY(M10)+HOUR(M10),":",MINUTE(M10))
S3:S9	S3	=H3+M3+N3+P3
S10	S10	=SUM(S3:S9)-Q10
I13	I13	=#REF!+#REF!+#REF!+#REF!+I10
L13	L13	=L10
S18	S18	=S10*23.185%
S19	S19	=S10-S18

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

 

[HappyChappy]

the adjustment col is because i couldnt work out how to deduct for standing time when vehicle was broken down. so a quick fix to make it match the payslip was to just deduct the q col as a temp fix

 

[HappyChappy]

the 1 @ 17.5 was for 100 miles at .017 i enter 20 miles per day tey calc it a 1 unit on payslip

 

[Logit]

Does the above make any sense ?