+ Reply to Thread
Results 1 to 3 of 3

Help with determining the number of regular vs. overtime hours within a set time frame

  1. #1
    Registered User
    Join Date
    04-05-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Talking Help with determining the number of regular vs. overtime hours within a set time frame

    Hi everybody! I'm new to this site but I have a good amount of experience with Excel. I've hit a roadblock trying to come up with a formula that I can't figure out and was hoping somebody might have an answer or at least a nudge in the right direction. I'm working on a spreadsheet for entering the number of hours worked by employees but I'm trying to have it calculate the number of regular and overtime hours based on the start and end times that were worked and I can't figure it out.

    Regular time is considered any time worked between 8:00 AM and 4:00 PM. Any time worked outside of those times is considered overtime. I have four columns that I'm working with that need to be filled in for each employee - start time, arrival at the site, departure from the site, and end time. Basically an employee would enter the time they leave their office location, the time they get to and leave the project site, and the time they get back to their office location. Any time within any of those 4 columns that falls outside of the 8:00 AM to 4:00 PM window would be overtime. Any suggestions???

    Here's an example: The employee left for the site at 6:00 AM, arrived at 6:30 AM, worked at the site until 2:00 PM and arrived back at the office at 2:30 PM. That would add up to 6.5 regular hours and 2 overtime hours, but I need a formula to determine that breakdown with the number of regular hours in one cell and the number of overtime hours in another cell. So I guess it's really two formulas, but if someone can give me help with one then I can probably figure the other one out.

    The times in each column are selected from a drop down where the user can select a time in quarter hour increments starting at 12:00 AM and ending at 12:00 AM. I'm not using military time but I could change it to that if that makes things easier. The times are formatted as Custom with the format "h:mm AM/PM". I was trying to use the TIMEVALUE function in some way along with the IF function but it keeps giving me an error whenever I try to convert my list of times to serial numbers using the TIMEVALUE function and I can't figure out why. Even when I change the formatting of the cells to one of the Time formats I still get the error.

    Thanks for the help!!!
    Last edited by niftysquirrel; 04-05-2013 at 11:51 AM.

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: Help with determining the number of regular vs. overtime hours within a set time frame

    Post a workbook with sample data and show what you want. Makes it so much easier to get started.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    04-05-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Help with determining the number of regular vs. overtime hours within a set time frame

    I managed to figure this out after much trial and error woohoo!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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