+ Reply to Thread
Results 1 to 10 of 10

Calculating the number of people in building based on arrival and departure times

  1. #1
    Registered User
    Join Date
    09-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    73

    Calculating the number of people in building based on arrival and departure times

    Hi

    I'm looking to record the number of people in the building at any one time based on arrival and departure times. So if person 1 arrives at 07:55 and leaves at 17:00 and person two arrives at 09:00 and leaves at 16:30 I would be able to show that there was 1 person in the building until 09:00, then there were two until 16:30, then just one until 17:00.

    I'm planning to do this with the data of 200-300 people on a 15 minute basis so it's not something I would want to do manually!

    Thanks in advance.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Calculating the number of people in building based on arrival and departure times

    When you are in Advanced reply, click on the paperclip to open the upload window.

    View Pic
    Martin

  3. #3
    Registered User
    Join Date
    09-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    73

    Re: Calculating the number of people in building based on arrival and departure times

    Example Hours.xlsx

    Example form attached. Thanks.

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculating the number of people in building based on arrival and departure times

    Assuming Cell E2 contains the time that you wish to evaluate number of people in building

    Then use in F2
    =SUMPRODUCT(--($B$2:$B$13<=E2),--($C$2:$C$13>=E2))
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Registered User
    Join Date
    09-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    73

    Re: Calculating the number of people in building based on arrival and departure times

    Quote Originally Posted by Ace_XL View Post
    Assuming Cell E2 contains the time that you wish to evaluate number of people in building

    Then use in F2
    =SUMPRODUCT(--($B$2:$B$13<=E2),--($C$2:$C$13>=E2))
    Problem solved. Super quick response. Many thanks.

  6. #6
    Registered User
    Join Date
    09-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    73

    Re: Calculating the number of people in building based on arrival and departure times

    Example Hours (V2).xlsx

    Another query related to this, how would I change the calculation to take into account times past midnight? For example, if someone arrives at 20:00 and leaves at 02:00 the next day, I would want them to show as being there up to midnight but it shows them also being there at midnight 20 hours before they arrive. Would I have to include the date of the arrival and departure in the equation?

  7. #7
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculating the number of people in building based on arrival and departure times

    Including the date will give you the results..see attached
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    73

    Re: Calculating the number of people in building based on arrival and departure times

    Quote Originally Posted by Ace_XL View Post
    Including the date will give you the results..see attached
    Thanks, is there a way to do this having the date in separate columns rather than the date and time together in the same cell?

  9. #9
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Calculating the number of people in building based on arrival and departure times

    Same logic..

    Just combine date and time in the sumproduct function. See attached
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-29-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    73

    Re: Calculating the number of people in building based on arrival and departure times

    Many thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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