+ Reply to Thread
Results 1 to 3 of 3

Calculate number of occupied hours within time range

  1. #1
    Registered User
    Join Date
    04-24-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2007
    Posts
    6

    Calculate number of occupied hours within time range

    Hi All,

    I was hoping for some help in regards to the following problem:

    -In Column A I have names (let's say, ship names)
    -In Column B I have a start time
    -In Colum C I have an end time
    -Column D is the total duration in hours (B1-A1) that the ship was 'active'

    What I'm struggling with, is how do I calculate the number of 'active' hours during daytime - let's say between 7AM-7PM?

    Appreciate any help I can get get as I've reached a mental block!

    Thanks.

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Calculate number of occupied hours within time range

    There may be better ways, but here's a suggestion: You have your columns for actual start and end times. Create another set of columns with formulas to determine the applicable start and end times, and use those to calculate your active hours. I've attached an example rather than try to explain in detail. This will probably only work if start & end times are in the same day. I didn't try it otherwise.

    ActiveHrs.xlsx
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Registered User
    Join Date
    04-24-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Calculate number of occupied hours within time range

    Thanks natefarm - I like the simplicity of your solution. It works for me.

    In case anyone's interested, I also found a formula-based solution from a different thread by 'daddylonglegs'. Link can be found here.

    Thanks again!
    Last edited by Moe2; 10-29-2014 at 05:02 PM.

+ 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. [SOLVED] Need formula to calculate number of hours in a time span that are between 6PM and 6AM
    By gibson2503 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-05-2014, 06:13 PM
  2. Calculate number of hours within range
    By miego in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-12-2014, 05:50 PM
  3. Replies: 1
    Last Post: 11-02-2011, 06:50 PM
  4. Replies: 1
    Last Post: 11-02-2011, 06:44 PM
  5. Replies: 2
    Last Post: 01-21-2010, 11:09 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