+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Payroll Worksheet

  1. #1
    Registered User
    Join Date
    03-29-2010
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    57

    Payroll Worksheet

    I am new to any type of advanced Excel formulas, and really need some help creating a payroll worksheet. The idea behind the worksheet, is to simply enter in employee start and stop time. Then have Excel make all the calculations telling me what to input into Quick Books. Nice simply and easy.

    Here's the problem:

    We have different type's of shifts: Splits, Up For Grabs, and Regular. I need a way for Excel to distinguish between the shifts. This way i can get a total for the number of hours, of each type of shift, because they have pay differentials.

    I also need a way for Excel to mark any hours over 40 as overtime, in addition to identifying holidays.

    I know this is a lot to ask, but i am confident with some proper guidance it can be done.

    I appreciate any feedback.

  2. #2
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel for Mac 16.31
    Posts
    72

    Re: Payroll Worksheet

    Hi Kmac, I am sure that this can be done too .

    From your post I suspect that you will have a page where you enter all the work times, and a page where you create a table where you specify the details for each type of shift. Then a VLOOKUP will allow you to get the correct values according to the shifts.

    Easiest is if you upload a sample worksheet though!

  3. #3
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Payroll Worksheet

    I have a few additional questions:
    1. Can an employee have more than one type of shift in a given week?
    2. If so, are the overtime hours applied starting after 40 hours regardless of the shift or applied directly to a given type of shift first?
    3. I assume overtime is 1.5 x normal pay for all shifts, including the pay differential. Is this correct?

    The way I'm envisioning it, you'll need columns for name, then for each date, you'll need type of shift, start time, and stop time. If an employee can only have one type of shift a week, then type of shift can be once per week. Sumproduct formulas can be used to add up the types of hours for each shift and convert them to $, including overtime. A sum formula can be used at the end to add up all the $ and hours.

    As deemo said, though, try mocking up something that at least looks like what you want and see what parts of it you can get to work on your own, then post it here with specific questions about what's not working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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