+ Reply to Thread
Results 1 to 2 of 2

Need a formula to calculate time elapsed within work shift based on clocked in hours

  1. #1
    Registered User
    Join Date
    10-10-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    MS Office 2010
    Posts
    2

    Need a formula to calculate time elapsed within work shift based on clocked in hours

    Hi all,

    Facing a challenge at work coming up with a formula for payroll, would appreciate any help.

    To preserve privacy of employees I have used Donald Trump and Hillary Clinton as the names in my example.
    In the payroll system we rely on time which is "clocked in" by employees when they sign in and out each day (C:D). They are given rosters and expected to adhere to these (I:J).
    So if Donald turns up half an hour late and says traffic was gridlocked, that's ok, Mr Trump will recover 30 minutes at the end of his shift. During this shift Donald may clock out for his lunch break if he has one.
    Meanwhile Hillary has arranged to come an hour early to work, because she needs to attend a meeting afterwards so will need to leave an hour early. Also ok, no harm done.
    The managers would like a way to track how many hours were worked within the rostered hours (Adherence to shift), as it has become difficult to track with so very many employees joining the company.

    A formula in K2 would need to check the following:
    • if employee was early then account for only the time after shift begins
    • if employee finished past the end of shift, account for only time up until shift ends


    I've attached a screenshot of what the raw data looks like, as well as a sample Excel file for tinkering.
    Formula in F2 is =SUMIFS(E$1:E2,A$1:A2,A2,B$1:B2,B2)*24


    President Hours.JPG

    Once the formula shows hours within roster, I will use a pivot to provide the total per person per day, then Donald and Hillary's managers will use this to determine who is the more reliable employee.
    Attached Files Attached Files
    Last edited by MCS12; 06-11-2016 at 02:18 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need a formula to calculate time elapsed within work shift based on clocked in hours

    Hi, welcome to the forum

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Replies: 11
    Last Post: 10-22-2015, 04:28 PM
  2. Elapsed time, respecting work hours
    By miren324 in forum Excel General
    Replies: 1
    Last Post: 03-24-2015, 05:06 PM
  3. Formula to work out time elapsed based on a UK tax year
    By mhatters in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-19-2015, 08:29 AM
  4. Formula to work out deductions based on hours and time
    By elmobram22 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2013, 12:13 PM
  5. Replies: 3
    Last Post: 07-21-2013, 05:45 PM
  6. [SOLVED] How do you calculate elapsed time in hours between times on different dates?
    By RPeruzzi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2013, 01:16 PM
  7. formula for summing time elapsed within a certain hours
    By s.j.diaz323 in forum Excel General
    Replies: 5
    Last Post: 09-18-2012, 02:48 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