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

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! 
0
Georgia (30)
6/22/2005 1:42:01 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
551 Views

Similar Articles

[PageSpeed] 34

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.

"GEORGIA" wrote:

> 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! 
0
BarbR (262)
6/22/2005 1:50:02 PM
=Text(A2,mmyyyy)

-- 
Regards,
Tom Ogilvy


"GEORGIA" <GEORGIA@discussions.microsoft.com> wrote in message
news:4677C2A0-B774-4E30-82CC-291DF3578FC3@microsoft.com...
> 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!


0
twogilvy (1078)
6/22/2005 1:50:28 PM
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 

"Tom Ogilvy" wrote:

> =Text(A2,mmyyyy)
> 
> -- 
> Regards,
> Tom Ogilvy
> 
> 
> "GEORGIA" <GEORGIA@discussions.microsoft.com> wrote in message
> news:4677C2A0-B774-4E30-82CC-291DF3578FC3@microsoft.com...
> > 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!
> 
> 
> 
0
Georgia (30)
6/22/2005 2:00:04 PM
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.



"GEORGIA" wrote:

> 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 
> 
> "Tom Ogilvy" wrote:
> 
> > =Text(A2,mmyyyy)
> > 
> > -- 
> > Regards,
> > Tom Ogilvy
> > 
> > 
> > "GEORGIA" <GEORGIA@discussions.microsoft.com> wrote in message
> > news:4677C2A0-B774-4E30-82CC-291DF3578FC3@microsoft.com...
> > > 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!
> > 
> > 
> > 
0
Marilyn (26)
6/22/2005 8:50:05 PM
On Wed, 22 Jun 2005 06:42:01 -0700, "GEORGIA"
<GEORGIA@discussions.microsoft.com> wrote:

>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
0
ronrosenfeld (3123)
6/22/2005 11:47:46 PM
On Wed, 22 Jun 2005 06:42:01 -0700, "GEORGIA"
<GEORGIA@discussions.microsoft.com> wrote:

>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
0
ronrosenfeld (3123)
6/22/2005 11:51:16 PM
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" wrote:

> On Wed, 22 Jun 2005 06:42:01 -0700, "GEORGIA"
> <GEORGIA@discussions.microsoft.com> wrote:
> 
> >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
> 
0
Georgia (30)
6/23/2005 2:09:06 AM
On Wed, 22 Jun 2005 19:09:06 -0700, "GEORGIA"
<GEORGIA@discussions.microsoft.com> wrote:

>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
0
ronrosenfeld (3123)
6/23/2005 12:30:37 PM
Reply:

Similar Artilces:

sorting date " MMM-YY" (Jan-05)
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! 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 o...

Change date from mm-dd-yyyy to MMM-YY in Access
How do I change a field which contains a date in a mm-dd- yyyy format into mmm-yy format. For Example: Ship Month = 03/01/2004 Convert "Ship Month" value to display "Mar-04" Hi Vince, Set the Format property of the field, and/or the control(s) that are displaying it, to "mmm-yy" On Tue, 20 Apr 2004 12:01:52 -0700, "Vince" <anonymous@discussions.microsoft.com> wrote: >How do I change a field which contains a date in a mm-dd- >yyyy format into mmm-yy format. > For Example: Ship Month = 03/01/2004 > Convert "Ship Month&qu...

cell format, custom type: mmm-yy; working with
A cell is referenced in an equation. That cells format contains a custom type: mmm-yy. How does a vba function work with that generally? Is it a string to be chopped up and worked with, or can you use some object property to get at the value or month and year? Cate, If you want to use the formatted value of the cell, you need to use the ..Text property of the range object For example: Sub Test() Dim myC As Range Set myC = ActiveCell With myC .Value = Now MsgBox CDbl(.Value) & " is the cell's underlying number value" .NumberFormat = "mmm-yy" ...