+ Reply to Thread
Results 1 to 4 of 4

[SOLVED] Calculate time worked inside Regular Hours and Over Time

  1. #1
    Registered User
    Join Date
    04-05-2015
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    2

    [SOLVED] Calculate time worked inside Regular Hours and Over Time

    Hi.
    I'm not really good with Excel so in the attached Excel file, there is no formula at all. Sorry.
    I need help to create a timesheet for my employees. We've been doing it manually all these years and it's tiring to calculate each month.

    I will try to explain what I want as clear as I can.

    1) Core (regular) hours is from 08:00 to 17:00 hours.

    2) Any hours before and after that will be considered as Overtime (1.5 X regular hours)

    3) Our employees checks in and out very randomly
    -for example, employee A checks in at 05:02hrs and checks out at 12:16hrs
    -and then the same employee checks in again at 14:29hrs and out again at 16:37hrs.
    -and once again checks in at 21:00 and out at 00:37
    -all in the same day.

    4) Some days they checks in and out only once in the same day. For same days, twice. It really depends on the how much work we have on that day.

    5) What I want is every minutes worked in the duration between 08:00hrs and 17:00 counts as Regular Hours Worked

    6) And every minutes worked outside the duration of the 9hours between 08:00hrs and 17:00hrs counts as Overtime.

    7) I just need the total Regular Hours Worked & the total of Overtime Hours Worked at the end of each month.

    Thanks.
    Attached Files Attached Files
    Last edited by myxamhatosis; 04-07-2015 at 09:13 PM.

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Calculate time worked inside Regular Hours and Over Time

    In K7:

    Please Login or Register  to view this content.
    And drag down.

    In L7:

    Please Login or Register  to view this content.
    And drag down.

    In M7:

    Please Login or Register  to view this content.
    And drag down.

    Make sure your formatting is set to time. These are also assuming you will not cross into another working day with overtime. If that may be the case, more work will need to be done to remedy that issue.
    Last edited by mcmahobt; 04-07-2015 at 09:53 AM.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    11-11-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Calculate time worked inside Regular Hours and Over Time

    Hi myxamhatosis,

    Expanding on mcmahobt's answer, I've attached a re-jigged file that should do all you wanted. I've added in a few new features too:

    • Drop down menus for Month and Year
    • Date column automatically populates depending on Month/Year selected
    • Ability to edit the Regular/Overtime working hours dynamically
    • Properly formated dates/times
    • Data validation on relevant fields to ensure accuracy
    • Roster contained in a Table instead of a standard Range

    Hope this helps you.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-05-2015
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    2

    Re: [SOLVED] Calculate time worked inside Regular Hours and Over Time

    Works really perfectly!!
    Thank you mcmahobt & ChrisMcKay for helping!
    This is awesome :D

+ 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: 7
    Last Post: 01-02-2015, 04:06 PM
  2. Replies: 2
    Last Post: 07-27-2012, 04:54 AM
  3. Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  4. Replies: 3
    Last Post: 01-20-2012, 01:35 PM
  5. Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-07-2012, 06:59 AM

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