Trying to find dates that overlap in Excel
up vote
0
down vote
favorite
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.
microsoft-excel
asked Nov 21 at 1:56
RefurbMike
1
add a comment |
up vote
0
down vote
favorite
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.
microsoft-excel
asked Nov 21 at 1:56
RefurbMike
1
Could you provide a sample data or screenshot about your problem? This will better for use to help you.
– Lee
Nov 22 at 9:06
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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.
microsoft-excel
asked Nov 21 at 1:56
RefurbMike
1
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.
microsoft-excel
microsoft-excel
asked Nov 21 at 1:56
RefurbMike
1
asked Nov 21 at 1:56
RefurbMike
1
asked Nov 21 at 1:56
RefurbMike
1
asked Nov 21 at 1:56
RefurbMike
1
asked Nov 21 at 1:56
RefurbMike
1
1
Could you provide a sample data or screenshot about your problem? This will better for use to help you.
– Lee
Nov 22 at 9:06
add a comment |
Could you provide a sample data or screenshot about your problem? This will better for use to help you.
– Lee
Nov 22 at 9:06
Could you provide a sample data or screenshot about your problem? This will better for use to help you.
– Lee
Nov 22 at 9:06
Could you provide a sample data or screenshot about your problem? This will better for use to help you.
– Lee
Nov 22 at 9:06
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
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.
answered Nov 21 at 3:28
Forward Ed
437213
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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.
answered Nov 21 at 3:28
Forward Ed
437213
add a comment |
up vote
0
down vote
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.
answered Nov 21 at 3:28
Forward Ed
437213
add a comment |
up vote
0
down vote
up vote
0
down vote
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.
answered Nov 21 at 3:28
Forward Ed
437213
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.
answered Nov 21 at 3:28
Forward Ed
437213
answered Nov 21 at 3:28
Forward Ed
437213
answered Nov 21 at 3:28
Forward Ed
437213
answered Nov 21 at 3:28
Forward Ed
437213
437213
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1377152%2ftrying-to-find-dates-that-overlap-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Could you provide a sample data or screenshot about your problem? This will better for use to help you.
– Lee
Nov 22 at 9:06