Excel - Advanced Filter with Dates
How can I get the 'Expected Result' using Advanced Filters without having to create adicional columns?
new picture
With different date format it works with the criteria.
Regards,
Elio Fernandes
excel filter
asked Nov 19 '18 at 16:57
Elio FernandesElio Fernandes
14312
add a comment |
How can I get the 'Expected Result' using Advanced Filters without having to create adicional columns?
new picture
With different date format it works with the criteria.
Regards,
Elio Fernandes
excel filter
asked Nov 19 '18 at 16:57
Elio FernandesElio Fernandes
14312
add a comment |
How can I get the 'Expected Result' using Advanced Filters without having to create adicional columns?
new picture
With different date format it works with the criteria.
Regards,
Elio Fernandes
excel filter
asked Nov 19 '18 at 16:57
Elio FernandesElio Fernandes
14312
How can I get the 'Expected Result' using Advanced Filters without having to create adicional columns?
new picture
With different date format it works with the criteria.
Regards,
Elio Fernandes
excel filter
excel filter
asked Nov 19 '18 at 16:57
Elio FernandesElio Fernandes
14312
asked Nov 19 '18 at 16:57
Elio FernandesElio Fernandes
14312
edited Nov 20 '18 at 12:40
Elio Fernandes
asked Nov 19 '18 at 16:57
Elio FernandesElio Fernandes
14312
asked Nov 19 '18 at 16:57
Elio FernandesElio Fernandes
14312
asked Nov 19 '18 at 16:57
Elio FernandesElio Fernandes
14312
14312
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Edited to clarify and reflect addition to OP
The Criteria in your first image would return nothing as both date/times are the same. That would be equivalent to looking for values that equal 19/11/2018 00:00:00. Since you have no entries exactly equal to that value, this will return no results.
However, it works when I change the right hand condition to <=20/11/2018 00:00:00. This will filter on any value between 19/11/2018 00:00:00 and 20/11/2018 00:00:00 which gives your expected result.
OBTW, while I don't think date formatting is the problem, I did not validate your formatting on my sheet because I used my system default instead.
The reason it works in your second image is that now you are working with dates, not dates and times. Your criteria of >=19/11/2018 and <=19/11/2018 still equates to =19/11/2018 but since you only have dates, all of the dates equal to 19/11/2018 meet the criteria. If you really want to filter on a date, not a range of dates, then you just need the one condition =19/11/2018.
Also, if additional columns are an issue, then you can filter in place with either the Advanced Filters or the AutoFilter without the need for additional columns for your output. For AutoFilter, select Date Filters then Custom Filter.
answered Nov 19 '18 at 18:18
Rey JunaRey Juna
31118
Can you explain me new picture posted
– Elio Fernandes
Nov 20 '18 at 12:41
Updated my answer to clarify and to address addition to OP.
– Rey Juna
Nov 20 '18 at 18:54
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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',
autoActivateHeartbeat: false,
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%2fstackoverflow.com%2fquestions%2f53379370%2fexcel-advanced-filter-with-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Edited to clarify and reflect addition to OP
The Criteria in your first image would return nothing as both date/times are the same. That would be equivalent to looking for values that equal 19/11/2018 00:00:00. Since you have no entries exactly equal to that value, this will return no results.
However, it works when I change the right hand condition to <=20/11/2018 00:00:00. This will filter on any value between 19/11/2018 00:00:00 and 20/11/2018 00:00:00 which gives your expected result.
OBTW, while I don't think date formatting is the problem, I did not validate your formatting on my sheet because I used my system default instead.
The reason it works in your second image is that now you are working with dates, not dates and times. Your criteria of >=19/11/2018 and <=19/11/2018 still equates to =19/11/2018 but since you only have dates, all of the dates equal to 19/11/2018 meet the criteria. If you really want to filter on a date, not a range of dates, then you just need the one condition =19/11/2018.
Also, if additional columns are an issue, then you can filter in place with either the Advanced Filters or the AutoFilter without the need for additional columns for your output. For AutoFilter, select Date Filters then Custom Filter.
answered Nov 19 '18 at 18:18
Rey JunaRey Juna
31118
Can you explain me new picture posted
– Elio Fernandes
Nov 20 '18 at 12:41
Updated my answer to clarify and to address addition to OP.
– Rey Juna
Nov 20 '18 at 18:54
add a comment |
Edited to clarify and reflect addition to OP
The Criteria in your first image would return nothing as both date/times are the same. That would be equivalent to looking for values that equal 19/11/2018 00:00:00. Since you have no entries exactly equal to that value, this will return no results.
However, it works when I change the right hand condition to <=20/11/2018 00:00:00. This will filter on any value between 19/11/2018 00:00:00 and 20/11/2018 00:00:00 which gives your expected result.
OBTW, while I don't think date formatting is the problem, I did not validate your formatting on my sheet because I used my system default instead.
The reason it works in your second image is that now you are working with dates, not dates and times. Your criteria of >=19/11/2018 and <=19/11/2018 still equates to =19/11/2018 but since you only have dates, all of the dates equal to 19/11/2018 meet the criteria. If you really want to filter on a date, not a range of dates, then you just need the one condition =19/11/2018.
Also, if additional columns are an issue, then you can filter in place with either the Advanced Filters or the AutoFilter without the need for additional columns for your output. For AutoFilter, select Date Filters then Custom Filter.
answered Nov 19 '18 at 18:18
Rey JunaRey Juna
31118
Can you explain me new picture posted
– Elio Fernandes
Nov 20 '18 at 12:41
Updated my answer to clarify and to address addition to OP.
– Rey Juna
Nov 20 '18 at 18:54
add a comment |
Edited to clarify and reflect addition to OP
The Criteria in your first image would return nothing as both date/times are the same. That would be equivalent to looking for values that equal 19/11/2018 00:00:00. Since you have no entries exactly equal to that value, this will return no results.
However, it works when I change the right hand condition to <=20/11/2018 00:00:00. This will filter on any value between 19/11/2018 00:00:00 and 20/11/2018 00:00:00 which gives your expected result.
OBTW, while I don't think date formatting is the problem, I did not validate your formatting on my sheet because I used my system default instead.
The reason it works in your second image is that now you are working with dates, not dates and times. Your criteria of >=19/11/2018 and <=19/11/2018 still equates to =19/11/2018 but since you only have dates, all of the dates equal to 19/11/2018 meet the criteria. If you really want to filter on a date, not a range of dates, then you just need the one condition =19/11/2018.
Also, if additional columns are an issue, then you can filter in place with either the Advanced Filters or the AutoFilter without the need for additional columns for your output. For AutoFilter, select Date Filters then Custom Filter.
answered Nov 19 '18 at 18:18
Rey JunaRey Juna
31118
Edited to clarify and reflect addition to OP
The Criteria in your first image would return nothing as both date/times are the same. That would be equivalent to looking for values that equal 19/11/2018 00:00:00. Since you have no entries exactly equal to that value, this will return no results.
However, it works when I change the right hand condition to <=20/11/2018 00:00:00. This will filter on any value between 19/11/2018 00:00:00 and 20/11/2018 00:00:00 which gives your expected result.
OBTW, while I don't think date formatting is the problem, I did not validate your formatting on my sheet because I used my system default instead.
The reason it works in your second image is that now you are working with dates, not dates and times. Your criteria of >=19/11/2018 and <=19/11/2018 still equates to =19/11/2018 but since you only have dates, all of the dates equal to 19/11/2018 meet the criteria. If you really want to filter on a date, not a range of dates, then you just need the one condition =19/11/2018.
Also, if additional columns are an issue, then you can filter in place with either the Advanced Filters or the AutoFilter without the need for additional columns for your output. For AutoFilter, select Date Filters then Custom Filter.
answered Nov 19 '18 at 18:18
Rey JunaRey Juna
31118
edited Nov 20 '18 at 18:53
answered Nov 19 '18 at 18:18
Rey JunaRey Juna
31118
answered Nov 19 '18 at 18:18
Rey JunaRey Juna
31118
answered Nov 19 '18 at 18:18
Rey JunaRey Juna
31118
31118
Can you explain me new picture posted
– Elio Fernandes
Nov 20 '18 at 12:41
Updated my answer to clarify and to address addition to OP.
– Rey Juna
Nov 20 '18 at 18:54
add a comment |
Can you explain me new picture posted
– Elio Fernandes
Nov 20 '18 at 12:41
Updated my answer to clarify and to address addition to OP.
– Rey Juna
Nov 20 '18 at 18:54
Can you explain me new picture posted
– Elio Fernandes
Nov 20 '18 at 12:41
Can you explain me new picture posted
– Elio Fernandes
Nov 20 '18 at 12:41
Updated my answer to clarify and to address addition to OP.
– Rey Juna
Nov 20 '18 at 18:54
Updated my answer to clarify and to address addition to OP.
– Rey Juna
Nov 20 '18 at 18:54
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53379370%2fexcel-advanced-filter-with-dates%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
