+ Reply to Thread
Results 1 to 5 of 5

Hours between 2 intervals

  1. #1
    Registered User
    Join Date
    11-14-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Hours between 2 intervals

    I need a way to calculate hours between two intervals. Example 3:00pm and 1:00am should yield 10. I'm using this to determine shift length for a day, then adding up multiple days to get hours scheduled in a week.

    Currently I'm using "=b1-a1+(b1<a1)*24" where a1 is 3:00pm and b1 is 1:00am. This formula does yield the accurate result; however, when I attempt to add up multiple formulas like this to get a weekly total, my weekly total becomes astronomically high, and I have no idea why. I also always run into an issue where this becomes more difficult to calculate if the shift traverses midnight.

    Please help.

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Hours between 2 intervals

    If A1 and B1 contain Excel times of the form 12:34, the formula should be:

    "=( b1-a1+(b1<=a1) )*24

    The important thing is the addition of the outer parentheses.

    Changing "<" to "<=" is an improvement. It ensures that when A1 and B1 appear to be the same time, their difference is interpreted as 24 hours, not zero.

    We cannot say with impunity that will fix all of your problems because you neglected to provide specifics, namely a set up of pairs of numbers and the result that you get when you add them that is "astronomical".

  3. #3
    Registered User
    Join Date
    11-14-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Re: Hours between 2 intervals

    Hey joeu2004,

    My apologies for not clarifying. I've attached a file with an example of the issue I've ran into. Cells S2:Y2 are calculating the hours worked correctly; however, cell Z2, is yielding 116 hours, when the schedule shows the agent working 7.5 hours 5 days in the week.

    Hopefully this helps clarify. Let me know if you need something else.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Hours between 2 intervals

    First, since you multiply the Excel time arithmetic by 24, you are calculating decimal hours.

    So the format of S2:Y2 should be Number, just like Z2 and AA2.

    Second, you should apply the correction that I suggested previously.

    The formulas should be of the form:

    =IFERROR( ( F2-E2+(F2 <= E2) )*24,"")

    Then everything will be copacetic.

  5. #5
    Registered User
    Join Date
    11-14-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Re: Hours between 2 intervals

    Thank you! This worked great!

+ 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: 1
    Last Post: 09-27-2016, 12:41 AM
  2. Count the Hours between certain intervals
    By Ixorian in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-26-2015, 07:40 PM
  3. formula to fill large range of cells with time intervals exceeding 24 hours
    By mejia.j88 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2014, 11:59 PM
  4. Calculating quantiies from irregular intervals to regular intervals
    By abhi1421 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-15-2014, 06:34 AM
  5. [SOLVED] Copy With Intervals Paste With No Intervals.
    By gorinw10 in forum Excel General
    Replies: 12
    Last Post: 07-20-2013, 12:23 PM
  6. Replies: 6
    Last Post: 07-17-2012, 04:49 AM
  7. Replies: 1
    Last Post: 08-10-2010, 02:13 AM

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