Excel 2007 : 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.

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!

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.