Filter time range
We are trying to get a report as to how many tickets where logged out of hours.
Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which is displayed as Dec 2 2018 04:11 PM.
I need to filter that to show me a time range from 5pm to 8am ( out of hours) so that I can see how many ticket where out of hours in month
Can anyone help
Thanks
microsoft-excel time
asked Dec 19 '18 at 21:30
James
11
add a comment |
We are trying to get a report as to how many tickets where logged out of hours.
Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which is displayed as Dec 2 2018 04:11 PM.
I need to filter that to show me a time range from 5pm to 8am ( out of hours) so that I can see how many ticket where out of hours in month
Can anyone help
Thanks
microsoft-excel time
asked Dec 19 '18 at 21:30
James
11
1
Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.
– Worthwelle
Dec 19 '18 at 22:23
A detail sample will more helpful.
– Lee
Dec 24 '18 at 9:08
add a comment |
We are trying to get a report as to how many tickets where logged out of hours.
Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which is displayed as Dec 2 2018 04:11 PM.
I need to filter that to show me a time range from 5pm to 8am ( out of hours) so that I can see how many ticket where out of hours in month
Can anyone help
Thanks
microsoft-excel time
asked Dec 19 '18 at 21:30
James
11
We are trying to get a report as to how many tickets where logged out of hours.
Our ticketing system generates a report(which i can export to excel) giving me the "created time" of the ticket. Which is displayed as Dec 2 2018 04:11 PM.
I need to filter that to show me a time range from 5pm to 8am ( out of hours) so that I can see how many ticket where out of hours in month
Can anyone help
Thanks
microsoft-excel time
microsoft-excel time
asked Dec 19 '18 at 21:30
James
11
asked Dec 19 '18 at 21:30
James
11
asked Dec 19 '18 at 21:30
James
11
asked Dec 19 '18 at 21:30
James
11
asked Dec 19 '18 at 21:30
James
11
11
1
Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.
– Worthwelle
Dec 19 '18 at 22:23
A detail sample will more helpful.
– Lee
Dec 24 '18 at 9:08
add a comment |
1
Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.
– Worthwelle
Dec 19 '18 at 22:23
A detail sample will more helpful.
– Lee
Dec 24 '18 at 9:08
1
1
Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.
– Worthwelle
Dec 19 '18 at 22:23
Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.
– Worthwelle
Dec 19 '18 at 22:23
A detail sample will more helpful.
– Lee
Dec 24 '18 at 9:08
A detail sample will more helpful.
– Lee
Dec 24 '18 at 9:08
add a comment |
1 Answer
1
active
oldest
votes
I would like to suggest Two different methods will help you to fix the issue.
Method 1:
This needs a Helper Column and then Auto Filter.
- I'm assuming that you have Dates in Range
A4:A13. - Fill Cell
A1with Start Time & CellA2with End Time.
(check the Time criteria in Green color).
Enter this Formula in Cell
B4& fill it Down.
=IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")
Apply Auto Filter to required Range (
A4:B13) & FilterGood Time.
Method 2:
This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.
Note:
Source Data Range isA4:A13.
Criteria Cells areA1 & A2.
Counter is in CellC1.
Write this Formula in Cell C1.
=SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))
Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.
{=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}
You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.
answered Dec 20 '18 at 8:03
Rajesh S
3,7001522
add a comment |
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',
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%2fsuperuser.com%2fquestions%2f1386053%2ffilter-time-range%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
I would like to suggest Two different methods will help you to fix the issue.
Method 1:
This needs a Helper Column and then Auto Filter.
- I'm assuming that you have Dates in Range
A4:A13. - Fill Cell
A1with Start Time & CellA2with End Time.
(check the Time criteria in Green color).
Enter this Formula in Cell
B4& fill it Down.
=IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")
Apply Auto Filter to required Range (
A4:B13) & FilterGood Time.
Method 2:
This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.
Note:
Source Data Range isA4:A13.
Criteria Cells areA1 & A2.
Counter is in CellC1.
Write this Formula in Cell C1.
=SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))
Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.
{=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}
You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.
answered Dec 20 '18 at 8:03
Rajesh S
3,7001522
add a comment |
I would like to suggest Two different methods will help you to fix the issue.
Method 1:
This needs a Helper Column and then Auto Filter.
- I'm assuming that you have Dates in Range
A4:A13. - Fill Cell
A1with Start Time & CellA2with End Time.
(check the Time criteria in Green color).
Enter this Formula in Cell
B4& fill it Down.
=IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")
Apply Auto Filter to required Range (
A4:B13) & FilterGood Time.
Method 2:
This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.
Note:
Source Data Range isA4:A13.
Criteria Cells areA1 & A2.
Counter is in CellC1.
Write this Formula in Cell C1.
=SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))
Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.
{=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}
You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.
answered Dec 20 '18 at 8:03
Rajesh S
3,7001522
add a comment |
I would like to suggest Two different methods will help you to fix the issue.
Method 1:
This needs a Helper Column and then Auto Filter.
- I'm assuming that you have Dates in Range
A4:A13. - Fill Cell
A1with Start Time & CellA2with End Time.
(check the Time criteria in Green color).
Enter this Formula in Cell
B4& fill it Down.
=IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")
Apply Auto Filter to required Range (
A4:B13) & FilterGood Time.
Method 2:
This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.
Note:
Source Data Range isA4:A13.
Criteria Cells areA1 & A2.
Counter is in CellC1.
Write this Formula in Cell C1.
=SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))
Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.
{=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}
You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.
answered Dec 20 '18 at 8:03
Rajesh S
3,7001522
I would like to suggest Two different methods will help you to fix the issue.
Method 1:
This needs a Helper Column and then Auto Filter.
- I'm assuming that you have Dates in Range
A4:A13. - Fill Cell
A1with Start Time & CellA2with End Time.
(check the Time criteria in Green color).
Enter this Formula in Cell
B4& fill it Down.
=IF(AND(A4>=$A$1,A4<=$A$2), "Good Time", "Bad Time")
Apply Auto Filter to required Range (
A4:B13) & FilterGood Time.
Method 2:
This is little complex but better and faster, especially when you are trying to Filter Multiple Rows and Columns.
Note:
Source Data Range isA4:A13.
Criteria Cells areA1 & A2.
Counter is in CellC1.
Write this Formula in Cell C1.
=SUMPRODUCT(($A$4:$A$13>=$A$1)*($A$4:$A$13<=$A$2))
Enter this Array Formula in cell D4, finish with Ctrl+Shift+Enter and fill it Down.
{=IF(ROWS(A$4:A4)>$C$1,"",INDEX(A$4:A$13,SMALL(IF(($A$4:$A$13>=$A$1)*($A$14:$A$13<=$A$2),ROW(A$4:A$13)-ROW($A$4)+1),ROWS(A$4:A4))))}
You can adjust cell references for Criteria, Source Data, Counter and Filtered data as your need.
answered Dec 20 '18 at 8:03
Rajesh S
3,7001522
answered Dec 20 '18 at 8:03
Rajesh S
3,7001522
answered Dec 20 '18 at 8:03
Rajesh S
3,7001522
answered Dec 20 '18 at 8:03
Rajesh S
3,7001522
3,7001522
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%2f1386053%2ffilter-time-range%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
Since that's not a proper Excel date, you will need to either insert a helper column to convert to a proper Excel date format or use a macro to do so. After that you can properly filter between specific ranges.
– Worthwelle
Dec 19 '18 at 22:23
A detail sample will more helpful.
– Lee
Dec 24 '18 at 9:08