Sort dates by month
 

I have imported data in the following format:
01-Sep-52
15-Apr-57
17-Mar-59
12-Aug-60
19-Aug-41
14-Apr-46
24-Nov-63
Is there a way to sort cells by month then day then year when the data is in
the same cell?

Sort dates by month
 

you could change the format of your date cells to show the date as
month/date/year then a normal sort would work

alternative is once you have hightlighted the data to sort goto sort and
click the options box then select the sort order as jan, feb ect

"dford" wrote:

I have imported data in the following format:
01-Sep-52
15-Apr-57
17-Mar-59
12-Aug-60
19-Aug-41
14-Apr-46
24-Nov-63
Is there a way to sort cells by month then day then year when the data is in
the same cell?

Sort dates by month
 

"you could change the format of your date cells to show the date as
month/date/year then a normal sort would work"

That's not true. Excel will sort chronologically by date, regardless of the
cell's format.

To sort by month, you need to create a helper cell, as in:

=month(a1)

or, if you want to sort by month and day, you're probably best off to create
dates with the same year, as in:

=date(year(today()),month(a1),day(a1)

--
Regards,
Fred


"Rich" (no spam) wrote in message
...
you could change the format of your date cells to show the date as
month/date/year then a normal sort would work

alternative is once you have hightlighted the data to sort goto sort and
click the options box then select the sort order as jan, feb ect

"dford" wrote:

I have imported data in the following format:
01-Sep-52
15-Apr-57
17-Mar-59
12-Aug-60
19-Aug-41
14-Apr-46
24-Nov-63
Is there a way to sort cells by month then day then year when the data is in
the same cell?

Sort dates by month
 

Not True . as in your not sure how that works or not true as in its not going
to work exactly the way he wants it

em guess the version of excell i have must be wired diffrent cause emm if
you hit the options dialog box in sort it does actually sort by month then
day of month if you ask nicly (or rather hit the right buttons)

of course the helpers rows are always a good idea but if your going to that
exctreme may as well just format the original date coloum

Rich


"Fred Smith" wrote:

"you could change the format of your date cells to show the date as
month/date/year then a normal sort would work"

That's not true. Excel will sort chronologically by date, regardless of the
cell's format.

To sort by month, you need to create a helper cell, as in:

=month(a1)

or, if you want to sort by month and day, you're probably best off to create
dates with the same year, as in:

=date(year(today()),month(a1),day(a1)

--
Regards,
Fred


"Rich" (no spam) wrote in message
...
you could change the format of your date cells to show the date as
month/date/year then a normal sort would work

alternative is once you have hightlighted the data to sort goto sort and
click the options box then select the sort order as jan, feb ect

"dford" wrote:

I have imported data in the following format:
01-Sep-52
15-Apr-57
17-Mar-59
12-Aug-60
19-Aug-41
14-Apr-46
24-Nov-63
Is there a way to sort cells by month then day then year when the data is in
the same cell?

Sort dates by month
 

I'd use a helper column with a bunch of formulas like:

=text(a1,"mmddyyyy")

then sort all the data by that extra column.



dford wrote:

I have imported data in the following format:
01-Sep-52
15-Apr-57
17-Mar-59
12-Aug-60
19-Aug-41
14-Apr-46
24-Nov-63
Is there a way to sort cells by month then day then year when the data is in
the same cell?

--

Dave Peterson

Sort dates by month
 

It didn't work for me, either.

I put a bunch of dates in A1:A365

I formatted them as:
mmm dd, yyyy

I selected that column and did data|sort, clicked on options, chose Jan, Feb...

And my dates were sorted in date order--not by month.

I use xl2003.

Rich wrote:

Not True . as in your not sure how that works or not true as in its not going
to work exactly the way he wants it

em guess the version of excell i have must be wired diffrent cause emm if
you hit the options dialog box in sort it does actually sort by month then
day of month if you ask nicly (or rather hit the right buttons)

of course the helpers rows are always a good idea but if your going to that
exctreme may as well just format the original date coloum

Rich

"Fred Smith" wrote:

"you could change the format of your date cells to show the date as
month/date/year then a normal sort would work"

That's not true. Excel will sort chronologically by date, regardless of the
cell's format.

To sort by month, you need to create a helper cell, as in:

=month(a1)

or, if you want to sort by month and day, you're probably best off to create
dates with the same year, as in:

=date(year(today()),month(a1),day(a1)

--
Regards,
Fred


"Rich" (no spam) wrote in message
...
you could change the format of your date cells to show the date as
month/date/year then a normal sort would work

alternative is once you have hightlighted the data to sort goto sort and
click the options box then select the sort order as jan, feb ect

"dford" wrote:

I have imported data in the following format:
01-Sep-52
15-Apr-57
17-Mar-59
12-Aug-60
19-Aug-41
14-Apr-46
24-Nov-63
Is there a way to sort cells by month then day then year when the data is in
the same cell?





--

Dave Peterson

Sort dates by month
 

The Options box in a Sort can be used if you have *text* like Jan, Feb, Mar...
then it will be sorted in month order. If you have a list of dates, it will be
sorted in chronological order, regardless of the format.

--
Regards,
Fred
Excel XP


"Rich" (no spam) wrote in message
...
Not True . as in your not sure how that works or not true as in its not going
to work exactly the way he wants it

em guess the version of excell i have must be wired diffrent cause emm if
you hit the options dialog box in sort it does actually sort by month then
day of month if you ask nicly (or rather hit the right buttons)

of course the helpers rows are always a good idea but if your going to that
exctreme may as well just format the original date coloum

Rich


"Fred Smith" wrote:

"you could change the format of your date cells to show the date as
month/date/year then a normal sort would work"

That's not true. Excel will sort chronologically by date, regardless of the
cell's format.

To sort by month, you need to create a helper cell, as in:

=month(a1)

or, if you want to sort by month and day, you're probably best off to create
dates with the same year, as in:

=date(year(today()),month(a1),day(a1)

--
Regards,
Fred


"Rich" (no spam) wrote in message
...
you could change the format of your date cells to show the date as
month/date/year then a normal sort would work

alternative is once you have hightlighted the data to sort goto sort and
click the options box then select the sort order as jan, feb ect

"dford" wrote:

I have imported data in the following format:
01-Sep-52
15-Apr-57
17-Mar-59
12-Aug-60
19-Aug-41
14-Apr-46
24-Nov-63
Is there a way to sort cells by month then day then year when the data is
in
the same cell?