sorting date " MMM-YY" (Jan-05)

[Guest]

Hi,
I have a spreadsheets with ticket numbers and the date it was submitted. It
is in medium date format (3/18/2005). I have used this formula:
=text(A2,"MMM-YY") to conver it to Mar-05.
now i have a column with MMMYYY date, however, it will not let me sort it.
It is sorting alphabetically, and not in the order of the date. so I have
Feb-04,Feb-05, Jan-04,Jan-05 and so on. How can I sort this by month then
year?

Thank you!

 

[Guest]

It is sorting alphabetically because the data is in TEXT format. In order
to sort by date, you'll need to have the data formatted in some kind of DATE
format. I'd guess that the type "Mar-01" is the one that you'd want. If
you want to sort by MONTH and YEAR, you'll have to have a column of MONTH
only and another of YEAR only and sort by those.

If someone has a more elegant solution, please let me know.

 

[Tom Ogilvy]

=Text(A2,mmyyyy)

 

[Guest]

this converts to format (012005)
i want to conver it to MMM-YY which is Jan-05 or even Jan05 then sort.
any help would appreciated it.
thank you

 

[Guest]

I have an additional question regarding the date as text. I have imported
data and it automatically converts to this format except pre- 2k the address
is correct, post 2k it converts to DD-MMM. The only way I've been able to
fix this is to use the SHEET.xlt instead of BOOK.xlt and then set the errors
to allow two digit years.

 

[Ron Rosenfeld]

Hi,
I have a spreadsheets with ticket numbers and the date it was submitted. It
is in medium date format (3/18/2005). I have used this formula:
=text(A2,"MMM-YY") to conver it to Mar-05.
now i have a column with MMMYYY date, however, it will not let me sort it.
It is sorting alphabetically, and not in the order of the date. so I have
Feb-04,Feb-05, Jan-04,Jan-05 and so on. How can I sort this by month then
year?

Thank you!

I am assuming your dates are in column A2:A1000, and that A1 contains an
appropriate header (e.g. Date)

Here is one method:

Insert two columns to the left of column A (the original column A will now be
Column C).

A1: Month
B1: Year

A2: =MONTH(C2)
B2: =YEAR(C2)

Copy/Drag these formulas down to row 1000.

Select a cell in the table; then Data/Sort

Sort by Month; Ascending
then by Year; Ascending

Finally, you can delete columns A & B.

If you want the dates in the DATE column to appear as mmm-yy then select that
column and Format/Cells/Number/Custom Type: mmm-yy


--ron

 

[Ron Rosenfeld]

Hi,
I have a spreadsheets with ticket numbers and the date it was submitted. It
is in medium date format (3/18/2005). I have used this formula:
=text(A2,"MMM-YY") to conver it to Mar-05.
now i have a column with MMMYYY date, however, it will not let me sort it.
It is sorting alphabetically, and not in the order of the date. so I have
Feb-04,Feb-05, Jan-04,Jan-05 and so on. How can I sort this by month then
year?

Thank you!

Tom's solution is a bit simpler than mine.

But again, to display the MMM-YY format, merely format the cells in your Date
column accordingly, rather than convert them to a text string.


--ron

 

[Guest]

I apologize for not being clear..
i guess I wanted to convert the long date 1-11-05 to Jan-05 for pivot table
purpose.
If i change the format on 1-11-05 as MMM-YY... it will show up as MMM-YY but
when you click on that actual cell, it is still 1-11-05, therefore when I do
the pivot table, it will not group all Jan-05 together. my pivot shows in
alphabetic order instead of Date order. For example: April04,April05,
Feb04,Feb05 and so on.
Someone help! Thank you!

 

[Ron Rosenfeld]

I apologize for not being clear..
i guess I wanted to convert the long date 1-11-05 to Jan-05 for pivot table
purpose.
If i change the format on 1-11-05 as MMM-YY... it will show up as MMM-YY but
when you click on that actual cell, it is still 1-11-05, therefore when I do
the pivot table, it will not group all Jan-05 together. my pivot shows in
alphabetic order instead of Date order. For example: April04,April05,
Feb04,Feb05 and so on.
Someone help! Thank you!

If you are using a pivot table, that makes it much easier. For example, using
two columns, with a list of dates in one column, and a "ticket number" in the
adjacent column, I generated a pivot table. Format cells in Date column as
mmm-yy

1. Drag Dates to Row area
2. Drag Ticket Number to Data area and select to do Count (Field Settings)
3. Click in Row area, Right Click/Group and Show Detail/Group/ select Months
and Years.

This will initially set up with Years in the First Column and Months in
the Second column like this:

2004 Jan 34
Feb 24
Mar 32
Apr 30
May 26
Jun 37
Jul 28
Aug 35
Sep 23
Oct 33
Nov 21
Dec 24
2005 Jan 32
Feb 21
Mar 36
Apr 32
May 29
Jun 32
Jul 30
Aug 30
Sep 32
Oct 32
Nov 40
Dec 31
2006 Jan 36
Feb 19
Mar 27
Apr 31
May 32
Jun 30
Jul 26
Aug 26
Sep 31
Oct 37
Nov 37
Dec 28


=============
However, you can drag the Years column to the right of the months column and it
will then sort as you have specified:

Count of Ticket Number
Date Years Total
Jan 2004 34
2005 32
2006 36
2007 26
2008 29
Jan Total 157
Feb 2004 24
2005 21
2006 19
2007 25
2008 28
Feb Total 117
Mar 2004 32
2005 36
2006 27
2007 30
2008 31
Mar Total 156
============================

No formulas or anything special required!



--ron