# Lorry driver shifts (day,night,saturday, sunday) variable payrate

1. ## Lorry driver shifts (day,night,saturday, sunday) variable payrate

Hi there,

Short description I have attached the xlx sheet.

I have recently joined this forum, I used to work with excel in the school but I am rusty now I have not used it for a long time.
I am a truck driver and I always have to calculate my weekly wage on paper.
I start everyday at different times it varies the end of shift as well. We have day shift, night, saturday, sunday payrates.
I have done an excel sheet I was trying for a couple of days reading forums and watching videos but just I couldn`t.

Example if I start Friday at 18:00 and finisth Saturday at 04:00. I have in total 10 hours.

Day shift: 1 hour
Night shift: 5 hour
Saturday: 4 hour

So in one shift I get 3 different payrate. In one shift if I work more than 6 hours it must be deducted 00:45 minutes.

Day shift: 07:00-19:00 - £10.7
Night shift: 19:00-07:00 - £11.7
Saturday - £12.7
Sunday - £14.7

P.s. in the case of very quick response which works I am willing to give donation.

Kind regards,

Apor Portik

2. ## Re: Lorry driver shifts (day,night,saturday, sunday) variable payrate

If the shift is more than 6 hours, the 45 minute deduction (presumably for a rest break) comes from which pay rate? Is it the one in which the six hour time is hit, the highest, the lowest or something else?

3. ## Re: Lorry driver shifts (day,night,saturday, sunday) variable payrate

Usually my employer takes from the total from the end it depends which is more suitable for him but it is simplier just take it away from the last hour of the shift.

Thanks

Apor

4. ## Re: Lorry driver shifts (day,night,saturday, sunday) variable payrate

One approach is to use a user defined function. This one takes each minute of the shift and works out the appropriate category and finally sums and calculates the overall value.

``Please Login or Register  to view this content.``
Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In L4, enter =CalculatePay(C4,D4,E4) and copy down.

Remember to save the workbook as a macro enabled workbook .xlsm

5. ## Re: Lorry driver shifts (day,night,saturday, sunday) variable payrate

Hi Martin,

Thank your for your very quick answer the formula works brilliantly, there was just 2 pounds difference to my paper calculations but that is ok because my calculation always differs from my employer`s with a few quid but that is the way.
I do not want to be pushy but if you have time could you please help me out with the other cells like DAY/NIGHT/SATURDAY/SUNDAY so it would show there the partition hours from the total and when a cell is blank no data (no working day) it would put 0 at the end.

Thank you

Apor

6. ## Re: Lorry driver shifts (day,night,saturday, sunday) variable payrate

``Please Login or Register  to view this content.``
In G4, enter =CalculatePayDay(C4,D4,E4) and copy down. Repeat for the other three.

7. ## Re: Lorry driver shifts (day,night,saturday, sunday) variable payrate

Hi Martin,
Thank you very much. Best and quickest answers I have got on any forum. I owe you.
The donation I have done it today to your chosen Charity.
I have got the impulse now to try out VBA with different parameters.
In any case if I can give you a hand when is needed, I will do it with pleasure. Unfortunately with Excel/VBA I can not help you out.

Kind regards,
Apor

8. ## Re: Lorry driver shifts (day,night,saturday, sunday) variable payrate

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -

Incase your issue is not solved, you can undo it as follows -

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

There are currently 1 users browsing this thread. (0 members and 1 guests)