excel formula to find date between two columns
up vote
0
down vote
favorite
I'm newbie to excel. So please forgive me if anything found silly.
I have two sheets with couple of similar columns, rest everything is different. Now what I was doing is:
In first sheet (say sheet1) I have columns like this (I have more than 4 columns, this is an example):
In the second sheet (say sheet2), I have some more data with 3 columns(col A,C,D) of the sheet1 being the same in sheet2. Something like this:
What I need: I want a result column in
sheet1which will give me thecolA valueif it falls betweenDateStartandDateEndor else it will befalsebased on the 1st column comparison i.e,someDatacol.
Sheet1 and Sheet2 has only 3 identical columns with some different data columns. I want two condition to be true here.
DateToFindfalls betweenDateStartandDateEnd.
Sheet1!someData = Sheet2!someData.
When this two conditions are true than only I want true or false as result.
I have tried different ways but I ended up with a if which gives me correct result for some rows but shows false for some rows even if the someData columns matches and the date falls in between colC and colD.
The last formula that I could come up with is:
=IF(AND(B2 > SHEET2!E:E, B2 < SHEET2!F:F, A = SHEET2!A:A), SHEET2!A2, FALSE)
Can anyone please help me with this? Thanks in advance.
microsoft-excel worksheet-function
asked Nov 27 at 12:34
aCoder
1147
|
show 1 more comment
up vote
0
down vote
favorite
I'm newbie to excel. So please forgive me if anything found silly.
I have two sheets with couple of similar columns, rest everything is different. Now what I was doing is:
In first sheet (say sheet1) I have columns like this (I have more than 4 columns, this is an example):
In the second sheet (say sheet2), I have some more data with 3 columns(col A,C,D) of the sheet1 being the same in sheet2. Something like this:
What I need: I want a result column in
sheet1which will give me thecolA valueif it falls betweenDateStartandDateEndor else it will befalsebased on the 1st column comparison i.e,someDatacol.
Sheet1 and Sheet2 has only 3 identical columns with some different data columns. I want two condition to be true here.
DateToFindfalls betweenDateStartandDateEnd.
Sheet1!someData = Sheet2!someData.
When this two conditions are true than only I want true or false as result.
I have tried different ways but I ended up with a if which gives me correct result for some rows but shows false for some rows even if the someData columns matches and the date falls in between colC and colD.
The last formula that I could come up with is:
=IF(AND(B2 > SHEET2!E:E, B2 < SHEET2!F:F, A = SHEET2!A:A), SHEET2!A2, FALSE)
Can anyone please help me with this? Thanks in advance.
microsoft-excel worksheet-function
asked Nov 27 at 12:34
aCoder
1147
1
It's unclear to me whatSheet2is for. You haveDateStartanddateEndalready onSheet1. And where iscolA value?Sheet1orSheet2?
– cybernetic.nomad
Nov 27 at 16:23
1
I'm also having trouble understanding the question. Sheets 1 and 2 seem to contain identical data, but have different additional columns. Sheet 2 doesn't seem to contain anything relevant to the task. It isn't clear why your formula does anything with sheet 2. If you're just trying to see if the DateToFind falls between DateStart and DateEnd, just compare them with>=and<=(the dates are stored as numbers). Your formula has the basic logic, you just seem to be applying it in the wrong place.
– fixer1234
Nov 27 at 21:42
yes guys.Sheet1andSheet2has identical columns with some different data columns. I want two condition to be true here. 1.DateToFind fall between DateStart and DateEnd 2. Sheet1!someData = Sheet2!someData. When this two conditions are true than only I want true or false as result. After this I want to extract columns from both the sheets and paste in a 3rd sheet. The data is huge so can't search manually. I have updated the question.
– aCoder
Nov 28 at 9:19
@fixer1234 yes the formula kind of works. But not for all the rows. After cross checking I came to know that the formula works only for few rows. But not for all.
– aCoder
Nov 28 at 9:45
It would help to have a "worked" example. Expand your data if necessary, but include examples of the full range of conditions (things that should work and things that shouldn't). Use that data to show what results you expect ("before vs. after") and the logic why. The data details also aren't clear. Aside from the additional columns, is the common data identical on both sheets? If not, how is it different (each can have different "someData", and you're just interested in the overlap? Is the data in the same sequence on both sheets? (cont'd)
– fixer1234
Nov 28 at 9:47
|
show 1 more comment
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm newbie to excel. So please forgive me if anything found silly.
I have two sheets with couple of similar columns, rest everything is different. Now what I was doing is:
In first sheet (say sheet1) I have columns like this (I have more than 4 columns, this is an example):
In the second sheet (say sheet2), I have some more data with 3 columns(col A,C,D) of the sheet1 being the same in sheet2. Something like this:
What I need: I want a result column in
sheet1which will give me thecolA valueif it falls betweenDateStartandDateEndor else it will befalsebased on the 1st column comparison i.e,someDatacol.
Sheet1 and Sheet2 has only 3 identical columns with some different data columns. I want two condition to be true here.
DateToFindfalls betweenDateStartandDateEnd.
Sheet1!someData = Sheet2!someData.
When this two conditions are true than only I want true or false as result.
I have tried different ways but I ended up with a if which gives me correct result for some rows but shows false for some rows even if the someData columns matches and the date falls in between colC and colD.
The last formula that I could come up with is:
=IF(AND(B2 > SHEET2!E:E, B2 < SHEET2!F:F, A = SHEET2!A:A), SHEET2!A2, FALSE)
Can anyone please help me with this? Thanks in advance.
microsoft-excel worksheet-function
asked Nov 27 at 12:34
aCoder
1147
I'm newbie to excel. So please forgive me if anything found silly.
I have two sheets with couple of similar columns, rest everything is different. Now what I was doing is:
In first sheet (say sheet1) I have columns like this (I have more than 4 columns, this is an example):
In the second sheet (say sheet2), I have some more data with 3 columns(col A,C,D) of the sheet1 being the same in sheet2. Something like this:
What I need: I want a result column in
sheet1which will give me thecolA valueif it falls betweenDateStartandDateEndor else it will befalsebased on the 1st column comparison i.e,someDatacol.
Sheet1 and Sheet2 has only 3 identical columns with some different data columns. I want two condition to be true here.
DateToFindfalls betweenDateStartandDateEnd.
Sheet1!someData = Sheet2!someData.
When this two conditions are true than only I want true or false as result.
I have tried different ways but I ended up with a if which gives me correct result for some rows but shows false for some rows even if the someData columns matches and the date falls in between colC and colD.
The last formula that I could come up with is:
=IF(AND(B2 > SHEET2!E:E, B2 < SHEET2!F:F, A = SHEET2!A:A), SHEET2!A2, FALSE)
Can anyone please help me with this? Thanks in advance.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Nov 27 at 12:34
aCoder
1147
asked Nov 27 at 12:34
aCoder
1147
edited Nov 28 at 9:23
asked Nov 27 at 12:34
aCoder
1147
asked Nov 27 at 12:34
aCoder
1147
asked Nov 27 at 12:34
aCoder
1147
1147
1
It's unclear to me whatSheet2is for. You haveDateStartanddateEndalready onSheet1. And where iscolA value?Sheet1orSheet2?
– cybernetic.nomad
Nov 27 at 16:23
1
I'm also having trouble understanding the question. Sheets 1 and 2 seem to contain identical data, but have different additional columns. Sheet 2 doesn't seem to contain anything relevant to the task. It isn't clear why your formula does anything with sheet 2. If you're just trying to see if the DateToFind falls between DateStart and DateEnd, just compare them with>=and<=(the dates are stored as numbers). Your formula has the basic logic, you just seem to be applying it in the wrong place.
– fixer1234
Nov 27 at 21:42
yes guys.Sheet1andSheet2has identical columns with some different data columns. I want two condition to be true here. 1.DateToFind fall between DateStart and DateEnd 2. Sheet1!someData = Sheet2!someData. When this two conditions are true than only I want true or false as result. After this I want to extract columns from both the sheets and paste in a 3rd sheet. The data is huge so can't search manually. I have updated the question.
– aCoder
Nov 28 at 9:19
@fixer1234 yes the formula kind of works. But not for all the rows. After cross checking I came to know that the formula works only for few rows. But not for all.
– aCoder
Nov 28 at 9:45
It would help to have a "worked" example. Expand your data if necessary, but include examples of the full range of conditions (things that should work and things that shouldn't). Use that data to show what results you expect ("before vs. after") and the logic why. The data details also aren't clear. Aside from the additional columns, is the common data identical on both sheets? If not, how is it different (each can have different "someData", and you're just interested in the overlap? Is the data in the same sequence on both sheets? (cont'd)
– fixer1234
Nov 28 at 9:47
|
show 1 more comment
1
It's unclear to me whatSheet2is for. You haveDateStartanddateEndalready onSheet1. And where iscolA value?Sheet1orSheet2?
– cybernetic.nomad
Nov 27 at 16:23
1
I'm also having trouble understanding the question. Sheets 1 and 2 seem to contain identical data, but have different additional columns. Sheet 2 doesn't seem to contain anything relevant to the task. It isn't clear why your formula does anything with sheet 2. If you're just trying to see if the DateToFind falls between DateStart and DateEnd, just compare them with>=and<=(the dates are stored as numbers). Your formula has the basic logic, you just seem to be applying it in the wrong place.
– fixer1234
Nov 27 at 21:42
yes guys.Sheet1andSheet2has identical columns with some different data columns. I want two condition to be true here. 1.DateToFind fall between DateStart and DateEnd 2. Sheet1!someData = Sheet2!someData. When this two conditions are true than only I want true or false as result. After this I want to extract columns from both the sheets and paste in a 3rd sheet. The data is huge so can't search manually. I have updated the question.
– aCoder
Nov 28 at 9:19
@fixer1234 yes the formula kind of works. But not for all the rows. After cross checking I came to know that the formula works only for few rows. But not for all.
– aCoder
Nov 28 at 9:45
It would help to have a "worked" example. Expand your data if necessary, but include examples of the full range of conditions (things that should work and things that shouldn't). Use that data to show what results you expect ("before vs. after") and the logic why. The data details also aren't clear. Aside from the additional columns, is the common data identical on both sheets? If not, how is it different (each can have different "someData", and you're just interested in the overlap? Is the data in the same sequence on both sheets? (cont'd)
– fixer1234
Nov 28 at 9:47
1
1
It's unclear to me what
Sheet2 is for. You have DateStart and dateEnd already on Sheet1. And where is colA value? Sheet1 or Sheet2?– cybernetic.nomad
Nov 27 at 16:23
It's unclear to me what
Sheet2 is for. You have DateStart and dateEnd already on Sheet1. And where is colA value? Sheet1 or Sheet2?– cybernetic.nomad
Nov 27 at 16:23
1
1
I'm also having trouble understanding the question. Sheets 1 and 2 seem to contain identical data, but have different additional columns. Sheet 2 doesn't seem to contain anything relevant to the task. It isn't clear why your formula does anything with sheet 2. If you're just trying to see if the DateToFind falls between DateStart and DateEnd, just compare them with
>= and <= (the dates are stored as numbers). Your formula has the basic logic, you just seem to be applying it in the wrong place.– fixer1234
Nov 27 at 21:42
I'm also having trouble understanding the question. Sheets 1 and 2 seem to contain identical data, but have different additional columns. Sheet 2 doesn't seem to contain anything relevant to the task. It isn't clear why your formula does anything with sheet 2. If you're just trying to see if the DateToFind falls between DateStart and DateEnd, just compare them with
>= and <= (the dates are stored as numbers). Your formula has the basic logic, you just seem to be applying it in the wrong place.– fixer1234
Nov 27 at 21:42
yes guys.
Sheet1 and Sheet2 has identical columns with some different data columns. I want two condition to be true here. 1.DateToFind fall between DateStart and DateEnd 2. Sheet1!someData = Sheet2!someData. When this two conditions are true than only I want true or false as result. After this I want to extract columns from both the sheets and paste in a 3rd sheet. The data is huge so can't search manually. I have updated the question.– aCoder
Nov 28 at 9:19
yes guys.
Sheet1 and Sheet2 has identical columns with some different data columns. I want two condition to be true here. 1.DateToFind fall between DateStart and DateEnd 2. Sheet1!someData = Sheet2!someData. When this two conditions are true than only I want true or false as result. After this I want to extract columns from both the sheets and paste in a 3rd sheet. The data is huge so can't search manually. I have updated the question.– aCoder
Nov 28 at 9:19
@fixer1234 yes the formula kind of works. But not for all the rows. After cross checking I came to know that the formula works only for few rows. But not for all.
– aCoder
Nov 28 at 9:45
@fixer1234 yes the formula kind of works. But not for all the rows. After cross checking I came to know that the formula works only for few rows. But not for all.
– aCoder
Nov 28 at 9:45
It would help to have a "worked" example. Expand your data if necessary, but include examples of the full range of conditions (things that should work and things that shouldn't). Use that data to show what results you expect ("before vs. after") and the logic why. The data details also aren't clear. Aside from the additional columns, is the common data identical on both sheets? If not, how is it different (each can have different "someData", and you're just interested in the overlap? Is the data in the same sequence on both sheets? (cont'd)
– fixer1234
Nov 28 at 9:47
It would help to have a "worked" example. Expand your data if necessary, but include examples of the full range of conditions (things that should work and things that shouldn't). Use that data to show what results you expect ("before vs. after") and the logic why. The data details also aren't clear. Aside from the additional columns, is the common data identical on both sheets? If not, how is it different (each can have different "someData", and you're just interested in the overlap? Is the data in the same sequence on both sheets? (cont'd)
– fixer1234
Nov 28 at 9:47
|
show 1 more comment
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2f1378747%2fexcel-formula-to-find-date-between-two-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f1378747%2fexcel-formula-to-find-date-between-two-columns%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
1
It's unclear to me what
Sheet2is for. You haveDateStartanddateEndalready onSheet1. And where iscolA value?Sheet1orSheet2?– cybernetic.nomad
Nov 27 at 16:23
1
I'm also having trouble understanding the question. Sheets 1 and 2 seem to contain identical data, but have different additional columns. Sheet 2 doesn't seem to contain anything relevant to the task. It isn't clear why your formula does anything with sheet 2. If you're just trying to see if the DateToFind falls between DateStart and DateEnd, just compare them with
>=and<=(the dates are stored as numbers). Your formula has the basic logic, you just seem to be applying it in the wrong place.– fixer1234
Nov 27 at 21:42
yes guys.
Sheet1andSheet2has identical columns with some different data columns. I want two condition to be true here. 1.DateToFind fall between DateStart and DateEnd 2. Sheet1!someData = Sheet2!someData. When this two conditions are true than only I want true or false as result. After this I want to extract columns from both the sheets and paste in a 3rd sheet. The data is huge so can't search manually. I have updated the question.– aCoder
Nov 28 at 9:19
@fixer1234 yes the formula kind of works. But not for all the rows. After cross checking I came to know that the formula works only for few rows. But not for all.
– aCoder
Nov 28 at 9:45
It would help to have a "worked" example. Expand your data if necessary, but include examples of the full range of conditions (things that should work and things that shouldn't). Use that data to show what results you expect ("before vs. after") and the logic why. The data details also aren't clear. Aside from the additional columns, is the common data identical on both sheets? If not, how is it different (each can have different "someData", and you're just interested in the overlap? Is the data in the same sequence on both sheets? (cont'd)
– fixer1234
Nov 28 at 9:47