how do i set up a duty roster to calculate payroll efficiently

[bastjanlee]

hi im suppose to come out with a duty roster with 6 employee and 3 different location.

i would like to know how best to set up my roster so that at the end of the month it would be easier for my to calculate their payroll by per hour basis

location A and B there will be only 1 employee (12pm - 8pm)
location C there will either be 1 employee working full shift (9.30am - 9.30pm) or 2 employee working 8 hours shift ( 9.30am - 5.30pm and 1.30pm - 9.30pm)

Location A and B: 8 hours (full shift: 12pm - 8pm)
Location C: 8 hours (half shift: 9.30am - 5.30pm or 1.30pm - 9.30pm), 12 hours (full shift)

[dflak]

Time management systems are best handled with databases. However with enough programming, you can get Excel to behave like a weak database with limited capacity. Tab B is exactly how you do not want to organize the data. This is what you want to see as a final report and it can be generated from the data.

I suggest you organize the data input like it is organized on the Time Cards sheet.

Take a look at each of the time cards and look at the formulas I've set up. Decide how many of these time cards you will need (how many store / employee combinations can be expected).

You fill out the week beginning date, the formulas on each time card fill in the date. You can copy / paste the time cards, but keep them all n columns A:H. The time cards read the holidays table and grays out any holidays for the week.

Fill in the stores and employees as indicated. Fill in the times.

The end result is that you want a program that reads these time cards and writes them to the data table shown in columns J:M. This data is in normalized form Whenever you design data for excel, it should be in normalized form.

I have not programmed the code to do this yet since there is some ambiguity. I do not understand what you want to see for Taka, Taka AM and Taka PM. Do you want hours worked at the store or number of hours the store is open? This may also affect how the time cards are laid out and how the data are laid out since if you are only interested in how many hours the store is open, then it does not matter who works at what store and that data can be maintained in a separate table.

[protonLeah]

title updated

[bastjanlee]

Hi Ben, sorry about that. i've made my thread title more concise now.

[bastjanlee]

I have not programmed the code to do this yet since there is some ambiguity. I do not understand what you want to see for Taka, Taka AM and Taka PM. Do you want hours worked at the store or number of hours the store is open? This may also affect how the time cards are laid out and how the data are laid out since if you are only interested in how many hours the store is open, then it does not matter who works at what store and that data can be maintained in a separate table.

ah yes. there's 2 shift at taka, one AM and one PM, so it's either filled with 2 employee (9.30am - 5.30pm and 1.30pm - 9.30pm) or by 1 employee covering the whole day (9.30am - 9.30pm)

[dflak]

Attach a new sample per the instructions in my last post.