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

1. ## 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!

View Pic

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

Example Hours.xlsx

Example form attached. Thanks.

4. ## 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))

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

Originally Posted by Ace_XL
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. ## 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. ## Re: Calculating the number of people in building based on arrival and departure times

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

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

Originally Posted by Ace_XL
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. ## 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

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

Many thanks!

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

#### 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