+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

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

    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)
    Attached Files Attached Files
    Last edited by bastjanlee; 04-19-2018 at 06:45 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: duty roster / payroll

    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.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,282

    Re: duty roster / payroll

    title updated
    Last edited by protonLeah; 04-19-2018 at 07:39 PM.
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

    Re: duty roster / payroll

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

  5. #5
    Registered User
    Join Date
    02-26-2018
    Location
    singapore
    MS-Off Ver
    office 365 mac
    Posts
    9

    Re: duty roster / payroll

    Quote Originally Posted by dflak View Post
    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)

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

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

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

+ 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. Duty Roster
    By nsprasad in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-22-2015, 02:55 PM
  2. OT- Need to create an 11 & 12 man Duty Roster
    By Gunjani in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2013, 07:56 AM
  3. Duty Roster in Excel
    By meddyuk in forum Excel General
    Replies: 1
    Last Post: 05-04-2013, 01:59 PM
  4. Rotating Duty Roster
    By rezabd99 in forum Excel General
    Replies: 0
    Last Post: 06-07-2012, 02:07 AM
  5. Rotating duty roster
    By the tax man in forum Excel General
    Replies: 3
    Last Post: 11-27-2011, 03:39 PM
  6. Duty Roster Formula
    By kennberg in forum Excel General
    Replies: 1
    Last Post: 08-08-2011, 08:16 AM
  7. Duty Roster
    By SavageMind in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2008, 04:44 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