+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    12-08-2013
    Location
    Reading
    MS-Off Ver
    Excel 2007
    Posts
    4

    Question 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

    Thank you for your replies.


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

    Kind regards,

    Apor Portik

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    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?
    Martin

  3. #3
    Registered User
    Join Date
    12-08-2013
    Location
    Reading
    MS-Off Ver
    Excel 2007
    Posts
    4

    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. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    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. #5
    Registered User
    Join Date
    12-08-2013
    Location
    Reading
    MS-Off Ver
    Excel 2007
    Posts
    4

    Thumbs up 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. #6
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

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

    You'll need some additional functions

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

  7. #7
    Registered User
    Join Date
    12-08-2013
    Location
    Reading
    MS-Off Ver
    Excel 2007
    Posts
    4

    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.
    Your response was more than helpful.
    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. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    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 -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    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.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Weekly average output for last entry on every Sunday night shifts
    By dinker454 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-18-2013, 11:51 AM
  2. saturday night joys :)
    By FDibbins in forum The Water Cooler
    Replies: 35
    Last Post: 01-22-2013, 09:00 AM
  3. Saturday Night
    By FDibbins in forum The Water Cooler
    Replies: 52
    Last Post: 01-14-2013, 07:45 AM
  4. Saturday Night Live
    By FDibbins in forum The Water Cooler
    Replies: 11
    Last Post: 12-03-2012, 05:13 AM
  5. Saturday night again
    By FDibbins in forum The Water Cooler
    Replies: 10
    Last Post: 11-19-2012, 01:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1