+ Reply to Thread
Results 1 to 3 of 3

[Problem] - Mutliple Sumifs / IF funcions over multiple columns - Pay Rate Timesheet

  1. #1
    Registered User
    Join Date
    04-16-2013
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    3

    [Problem] - Mutliple Sumifs / IF funcions over multiple columns - Pay Rate Timesheet

    Hello Everyone!

    Who is up for an excel brain teaser?! :P

    I have a troubling problem for my boss regarding employee timesheets and Queensland, Australia’s Enterprise Bargaining Agreements.

    Problem:
    Develop a spread sheet that my boss can input multiple employees’ daily start a and finish hours spent working per job code and reflect their hours based on:

    1. Standard (100% of pay rate): Based on total hours worked in a day up to 8hrs (but allow for a 30min lunch break bringing it to 7.5hrs)
    2. Over Time (150% of pay rate): Based on total hours worked in a day over 8hrs for a maximum of 2hrs
    3. Double Overtime (200% of pay rate) Hourly Rates: Based on total remaining hours worked in a day over 10hrs.

    Additionally, employees receive a mandatory 30min break between 12pm – 12:30pm which reduces their hours worked during that time by 30mins.

    IE: Say an employee can work 8hrs on JOB 1, and 2 hours on JOB 2. In this case JOB 1 would have 7.5 standard hours worked and JOB 2 would have 2 over time hours worked.

    See the attached spreadsheet for the layout and some example timesheet entries and their formula fields.

    Setup:

    Currently the spreadsheet’s user input columns set up like this:

    Date | Employee Name | Job Number | Start Time | Finish Time | Comments

    Note: The boss will input timesheet dates on a chronological basis as you go down the rows, ie: Dates and Times will increase and Job Numbers may jump all over the place.

    The following columns require formulas to automatically divide the time (located to the right of the columns above):

    Total Hours Worked | Standard Hours | Overtime Hours | Double Overtime Hours

    Examples:

    To get your head around some of the situations that may arise from hours worked on any one day, here are the following examples of columns split by a tab:

    Date Name Job Start Finish Total Standard (First 8hrs) Over Time(Max. 2hrs) Double Overtime
    18/4/15 Joe 1 6:00 9:00 3 3
    18/4/15 Joe 4 9:00 LUNCH 13:00 4 3.5
    18/4/15 Joe 2 13:00 17:00 4 0.5 2 0.5

    In the above example, 3 hours are worked in the morning on standard rates, 4 hours are worked on Job 4 but the 30min lunch break is deducted making it 3.5hrs, 4 hours are worked on Job 2 of which, 0.5hrs is put to standard hours to reach the max hours at standard pay to 8hrs, 2hours to overtime which is the maximum for that pay rate and the remaining 0.5hrs to double overtime for the day.

    Finally, I need all cells to be “” nothing if no data is placed into the date, employee name, job number, start and finish line.

    Challenge:

    I Timesheet Problem.xlsxu all in advance, would love to hear you solutions or just general feedback on how you would solve this excel problem.

    Regards,

    Michael
    Attached Files Attached Files
    Last edited by mvanarey; 04-19-2015 at 01:59 AM. Reason: Admin's request

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

    Re: [Problem] - Employee Pay Rate Timesheet

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

    * Please ask a specific question ...
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    04-16-2013
    Location
    Brisbane
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: [Problem] - Employee Pay Rate Timesheet

    Sorry about that, title changed.

+ 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. Creating an Employee Timesheet with Excel
    By Pamtrek in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-26-2014, 07:47 PM
  2. Calculating Day Rate and Night Rate Hours on a Timesheet
    By Hahnium in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-21-2013, 08:18 AM
  3. Calculating a timesheet for employee's
    By Sammy21 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-29-2012, 03:03 PM
  4. employee timesheet with overtime
    By tatehebert1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2008, 11:53 PM
  5. How do I calculate an employee timesheet in Excel?
    By Raven in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-03-2005, 03:05 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