+ Reply to Thread
Results 1 to 11 of 11

Determining how many hours are in a time range

  1. 08-04-2013, 09:07 PM #1
    lesoies
    lesoies is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Exclamation Determining how many hours are in a time range

    I have a spreadsheet with a shift start time and a shift end time. What I'm trying to do is figure out how much time is spent between a time range during their shift.

    For example, this is my working code:
    Please Login or Register  to view this content.
    This checks and returns how much time of the shift is spent between 7AM and 11AM. The only issue I'm having with this is the overnight shift. I can't figure out how to make this code work and return a valid number if, say, the shift was 11PM-7AM, or 6PM-2AM.

    If anybody could give me a hand with this I sure would appreciate it!!!
    Reply With QuoteRegister To Reply
  2. 08-04-2013, 09:20 PM #2
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: [URGENT FOR WORK] Determining how many hours are in a time range

    A lot easier if you upload the file or one with only the pertinent worksheet (or an example file thereof if real file contains sensitive info... provide at least one manual entry of expected result you want calculated by formula, and one of with matching current formula)...
    Reply With QuoteRegister To Reply
  3. 08-04-2013, 09:24 PM #3
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: [URGENT FOR WORK] Determining how many hours are in a time range

    hi lesoies. i agree with jhren. i'm guessing this will help though:
    =IF(OR(ISBLANK(C8),ISBLANK(D8)),0,MOD(D8-C8,1)*24)

    or:
    =IF(COUNT(C8:D8)<>2,0,MOD(D8-C8,1)*24)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  4. 08-04-2013, 09:39 PM #4
    lesoies
    lesoies is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: [URGENT FOR WORK] Determining how many hours are in a time range

    Here you are!

    Edit: Ignore the Dinner & Close columns, I realized there would be an error with overnight and skipped them :P
    Attached Files Attached Files
    Last edited by lesoies; 08-04-2013 at 09:46 PM.
    Reply With QuoteRegister To Reply
  5. 08-04-2013, 09:54 PM #5
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: [URGENT FOR WORK] Determining how many hours are in a time range

    You didn't include any expected results (manually enter if need be).

    Kind of hard to determine what you want by your formula if your formulas don't work...

    PS: What are the timeframes for each column Breakfast through Overnight.
    Last edited by jhren; 08-04-2013 at 09:57 PM.
    Reply With QuoteRegister To Reply
  6. 08-04-2013, 10:33 PM #6
    lesoies
    lesoies is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: [URGENT FOR WORK] Determining how many hours are in a time range

    Well, you can see how its broken... If you just change the end time to say, 5PM it will work correctly (for lunch & snack), but when I change the same formula to work for overnight, it has issues after passing the 24 hour mark.

    Breakfast - 7AM-11AM
    Lunch - 11AM - 2PM
    Snack - 2PM - 5PM
    Dinner - 5PM - 8PM
    Close - 8PM - 11PM
    Overnight - 11PM - 7AM

    Also sorry, I was rushing around and didn't proof read anything I posted.. My bad :P
    Reply With QuoteRegister To Reply
  7. 08-04-2013, 10:58 PM #7
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: [URGENT FOR WORK] Determining how many hours are in a time range

    Another question:

    Is it possible for anyone to work past 7AM the following morning from the date in E2? ...more than 24hrs straight?
    Last edited by jhren; 08-04-2013 at 11:05 PM.
    Reply With QuoteRegister To Reply
  8. 08-04-2013, 11:52 PM #8
    lesoies
    lesoies is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: Determining how many hours are in a time range

    I'm not sure I understand, but the start time could be 11PM on 07/03, and the end time 7AM or later on 7/04... As for having an employee scheduled to work 24 hours straight...no lol
    Reply With QuoteRegister To Reply
  9. 08-05-2013, 02:13 AM #9
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Determining how many hours are in a time range

    Let me rephrase the question, if someone were to work past 7AM the next morning, what is the earliest they would start work, even for an extremely unusual situation?
    Reply With QuoteRegister To Reply
  10. 08-05-2013, 03:05 AM #10
    lesoies
    lesoies is offline
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: Determining how many hours are in a time range

    I think I got it...

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 08-05-2013, 06:19 AM #11
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Determining how many hours are in a time range

    Didn't try your formula. See attached for what I came up with.
    Attached Files Attached Files
    Last edited by jhren; 08-05-2013 at 08:26 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Timesheet that calcs time in, time out, lunch used, reg hours, ot hours, double hours
    By noobface in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-21-2021, 03:45 AM
  2. [SOLVED] Help with determining the number of regular vs. overtime hours within a set time frame
    By niftysquirrel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2013, 12:52 PM
  3. [SOLVED] Determining if a reference time is within a certain time range
    By kweaver in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2012, 03:07 PM
  4. 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
  5. Excel 2007 : Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel General
    Replies: 0
    Last Post: 01-05-2012, 06:23 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1