0

Simple scenario: We have three computers in an office. Employees come in and out all day, using them. We want to know if we can reduce the office to two computers, by finding out if three people are using computers at the same time.

I have 3 columns: A is the name of the PC, B is date/time that a user starts a session, C is session end time.

I'm trying to figure out an equation that'll tell me how much overlap I have for these sessions. I've Googled around a bit, but haven't found something that fits this scenario.

1
  • Could you provide a sample data or screenshot about your problem? This will better for use to help you. – Lee Nov 22 '18 at 9:06
0

You can use sumproduct to count the number of occurrences in a column where a time is being used in the range of a time in another row.

Based on the set up of your data, you could use the following formula in D and copy down. Adjust the range to suit your needs. Since SUMPRODUCT is a function that performs array calculations, it is recommended that full column not be used.

=SUMPRODUCT((B1>=$B$1:$B$10)*(B1<=$C$1:$C$10))

Results of 3 tell you that three computers are in use as of the start time of the row where the 3 appears.

Results of greater than 3 tell you that something in your schedule is "wonky" because apparently that number of people are trying to use 3 computers at the same time.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.