Unsorted dates after sorting in Excel
Sometimes, even though I sort the column dates from early to late,
some dates remain unsorted.
I fix it by rewriting the dates manually, but this is inefficient.
microsoft-excel sorting date
edited Feb 6 at 15:44
Scott
15.9k113990
asked Feb 6 at 15:38
Alvaro MoralesAlvaro Morales
112
add a comment |
Sometimes, even though I sort the column dates from early to late,
some dates remain unsorted.
I fix it by rewriting the dates manually, but this is inefficient.
microsoft-excel sorting date
edited Feb 6 at 15:44
Scott
15.9k113990
asked Feb 6 at 15:38
Alvaro MoralesAlvaro Morales
112
add a comment |
Sometimes, even though I sort the column dates from early to late,
some dates remain unsorted.
I fix it by rewriting the dates manually, but this is inefficient.
microsoft-excel sorting date
edited Feb 6 at 15:44
Scott
15.9k113990
asked Feb 6 at 15:38
Alvaro MoralesAlvaro Morales
112
Sometimes, even though I sort the column dates from early to late,
some dates remain unsorted.
I fix it by rewriting the dates manually, but this is inefficient.
microsoft-excel sorting date
microsoft-excel sorting date
edited Feb 6 at 15:44
Scott
15.9k113990
asked Feb 6 at 15:38
Alvaro MoralesAlvaro Morales
112
edited Feb 6 at 15:44
Scott
15.9k113990
asked Feb 6 at 15:38
Alvaro MoralesAlvaro Morales
112
edited Feb 6 at 15:44
Scott
15.9k113990
edited Feb 6 at 15:44
Scott
15.9k113990
edited Feb 6 at 15:44
Scott
15.9k113990
15.9k113990
asked Feb 6 at 15:38
Alvaro MoralesAlvaro Morales
112
asked Feb 6 at 15:38
Alvaro MoralesAlvaro Morales
112
asked Feb 6 at 15:38
Alvaro MoralesAlvaro Morales
112
112
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
This looks like Excel is treating your G column as numerically formatted, rather than date formatted. Notice how each date is lined up in order as if it were a single large number?
Select your G column, hit ctrl-1 to open the format window, and select the appropriate date format.
answered Feb 6 at 15:44
Mikey T.K.Mikey T.K.
2,17731941
2
@Mikey-t-k When "/" is there twice, how can it be numerically formatted?
– VSRawat
Feb 6 at 18:36
add a comment |
Are you sure Excel understands that those values are dates?
Is your system set up to understand dd/mm/yyyy formatting?
If you put =G42+1 into Q42,
does it show you a date one day after the date in G42?
Can you change the date formatting to something like dd mmm yyyy
(in which the month name is spelled out)?
If you add an 17/08/2017entry to your list,
does it sort before all the 2018 dates,
or does it group with the other 17/08 dates?
It looks like Excel is treating those values as text strings
and sorting by the first few characters
(just as "agreed" sorts between "adroit" and "bakery").
Some parts of the world (most(?) of the English-speaking parts of the world?)
use mm/dd/yyyy, and if you're in one of those parts of the world
(or Excel thinks you are) it will interpret 12/08/2018 as December 8, 2018,
and it will interpret 17/08/2018 as a string,
because it doesn't make sense as a mm/dd/yyyy date.
answered Feb 6 at 15:55
ScottScott
15.9k113990
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%2f1402725%2funsorted-dates-after-sorting-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
This looks like Excel is treating your G column as numerically formatted, rather than date formatted. Notice how each date is lined up in order as if it were a single large number?
Select your G column, hit ctrl-1 to open the format window, and select the appropriate date format.
answered Feb 6 at 15:44
Mikey T.K.Mikey T.K.
2,17731941
2
@Mikey-t-k When "/" is there twice, how can it be numerically formatted?
– VSRawat
Feb 6 at 18:36
add a comment |
This looks like Excel is treating your G column as numerically formatted, rather than date formatted. Notice how each date is lined up in order as if it were a single large number?
Select your G column, hit ctrl-1 to open the format window, and select the appropriate date format.
answered Feb 6 at 15:44
Mikey T.K.Mikey T.K.
2,17731941
2
@Mikey-t-k When "/" is there twice, how can it be numerically formatted?
– VSRawat
Feb 6 at 18:36
add a comment |
This looks like Excel is treating your G column as numerically formatted, rather than date formatted. Notice how each date is lined up in order as if it were a single large number?
Select your G column, hit ctrl-1 to open the format window, and select the appropriate date format.
answered Feb 6 at 15:44
Mikey T.K.Mikey T.K.
2,17731941
This looks like Excel is treating your G column as numerically formatted, rather than date formatted. Notice how each date is lined up in order as if it were a single large number?
Select your G column, hit ctrl-1 to open the format window, and select the appropriate date format.
answered Feb 6 at 15:44
Mikey T.K.Mikey T.K.
2,17731941
answered Feb 6 at 15:44
Mikey T.K.Mikey T.K.
2,17731941
answered Feb 6 at 15:44
Mikey T.K.Mikey T.K.
2,17731941
answered Feb 6 at 15:44
Mikey T.K.Mikey T.K.
2,17731941
2,17731941
2
@Mikey-t-k When "/" is there twice, how can it be numerically formatted?
– VSRawat
Feb 6 at 18:36
add a comment |
2
@Mikey-t-k When "/" is there twice, how can it be numerically formatted?
– VSRawat
Feb 6 at 18:36
2
2
@Mikey-t-k When "/" is there twice, how can it be numerically formatted?
– VSRawat
Feb 6 at 18:36
@Mikey-t-k When "/" is there twice, how can it be numerically formatted?
– VSRawat
Feb 6 at 18:36
add a comment |
Are you sure Excel understands that those values are dates?
Is your system set up to understand dd/mm/yyyy formatting?
If you put =G42+1 into Q42,
does it show you a date one day after the date in G42?
Can you change the date formatting to something like dd mmm yyyy
(in which the month name is spelled out)?
If you add an 17/08/2017entry to your list,
does it sort before all the 2018 dates,
or does it group with the other 17/08 dates?
It looks like Excel is treating those values as text strings
and sorting by the first few characters
(just as "agreed" sorts between "adroit" and "bakery").
Some parts of the world (most(?) of the English-speaking parts of the world?)
use mm/dd/yyyy, and if you're in one of those parts of the world
(or Excel thinks you are) it will interpret 12/08/2018 as December 8, 2018,
and it will interpret 17/08/2018 as a string,
because it doesn't make sense as a mm/dd/yyyy date.
answered Feb 6 at 15:55
ScottScott
15.9k113990
add a comment |
Are you sure Excel understands that those values are dates?
Is your system set up to understand dd/mm/yyyy formatting?
If you put =G42+1 into Q42,
does it show you a date one day after the date in G42?
Can you change the date formatting to something like dd mmm yyyy
(in which the month name is spelled out)?
If you add an 17/08/2017entry to your list,
does it sort before all the 2018 dates,
or does it group with the other 17/08 dates?
It looks like Excel is treating those values as text strings
and sorting by the first few characters
(just as "agreed" sorts between "adroit" and "bakery").
Some parts of the world (most(?) of the English-speaking parts of the world?)
use mm/dd/yyyy, and if you're in one of those parts of the world
(or Excel thinks you are) it will interpret 12/08/2018 as December 8, 2018,
and it will interpret 17/08/2018 as a string,
because it doesn't make sense as a mm/dd/yyyy date.
answered Feb 6 at 15:55
ScottScott
15.9k113990
add a comment |
Are you sure Excel understands that those values are dates?
Is your system set up to understand dd/mm/yyyy formatting?
If you put =G42+1 into Q42,
does it show you a date one day after the date in G42?
Can you change the date formatting to something like dd mmm yyyy
(in which the month name is spelled out)?
If you add an 17/08/2017entry to your list,
does it sort before all the 2018 dates,
or does it group with the other 17/08 dates?
It looks like Excel is treating those values as text strings
and sorting by the first few characters
(just as "agreed" sorts between "adroit" and "bakery").
Some parts of the world (most(?) of the English-speaking parts of the world?)
use mm/dd/yyyy, and if you're in one of those parts of the world
(or Excel thinks you are) it will interpret 12/08/2018 as December 8, 2018,
and it will interpret 17/08/2018 as a string,
because it doesn't make sense as a mm/dd/yyyy date.
answered Feb 6 at 15:55
ScottScott
15.9k113990
Are you sure Excel understands that those values are dates?
Is your system set up to understand dd/mm/yyyy formatting?
If you put =G42+1 into Q42,
does it show you a date one day after the date in G42?
Can you change the date formatting to something like dd mmm yyyy
(in which the month name is spelled out)?
If you add an 17/08/2017entry to your list,
does it sort before all the 2018 dates,
or does it group with the other 17/08 dates?
It looks like Excel is treating those values as text strings
and sorting by the first few characters
(just as "agreed" sorts between "adroit" and "bakery").
Some parts of the world (most(?) of the English-speaking parts of the world?)
use mm/dd/yyyy, and if you're in one of those parts of the world
(or Excel thinks you are) it will interpret 12/08/2018 as December 8, 2018,
and it will interpret 17/08/2018 as a string,
because it doesn't make sense as a mm/dd/yyyy date.
answered Feb 6 at 15:55
ScottScott
15.9k113990
edited Feb 10 at 21:54
answered Feb 6 at 15:55
ScottScott
15.9k113990
answered Feb 6 at 15:55
ScottScott
15.9k113990
answered Feb 6 at 15:55
ScottScott
15.9k113990
15.9k113990
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.
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%2f1402725%2funsorted-dates-after-sorting-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
