Sorting Date Formats

[stan]

I'm having difficulty sorting a date fields in Excel. My
problem is I'm copying data from an Access query into an
Excel spreadsheet with a date column formatted as "mmm-
yy". The date data is coming over as text with a default
sort by the first letter of the month (i.e. Apr-03, Feb-
03, Jan-03, etc.) I've tried to format the column but it
still remains text. I'm needing to know how I can change
this to sort by the actual month and year (i.e. Jan-03,
Feb-03, Mar-03, etc.)

Any help would be greatly appreciated!

 

[Peo Sjoblom]

One way using a help column

=DATE(IF(--RIGHT(A1,2)>30,RIGHT(A1,2),RIGHT(A1,2)+2000),MATCH(LEFT(A1,3),{"J
AN";"FEB";"MAR";"APR";"MAY";"JUN";"JUL";"AUG";"SEP";"OCT";"NOV";"DEC"},0),1)

copy down as long as needed, copy and paste special as values in place,
now format as mmm-yy and sort

The above assumes your dates are in column A
I also suggest for the future using dates with 4 digit years

 

[Dave Peterson]

Another way using that same helper column idea:

=DATEVALUE(LEFT(A1,3)&" 1, "&IF(RIGHT(A1,2)>"30",1900,2000)+RIGHT(A1,2)*1)

 

[Dave Peterson]

and you could drop the final *1. It serves no purpose.

 

[stan]

Thanks for the help.

Do you know why when copying a date (such as Jan-03) from
Access to Excel, Excel makes it a text format and doesn't
retain the date format?

 

[stan]

Thanks for the help.

Do you know why when copying a date (such as Jan-03) from
Access to Excel, Excel makes it a text format and doesn't
retain the date format?

 

[Random]

How are you copying the data?

Random

Thanks for the help.

Do you know why when copying a date (such as Jan-03) from
Access to Excel, Excel makes it a text format and doesn't
retain the date format?

 

[stan]

Yes I am.

-----Original Message-----
How are you copying the data?

Random

 

[Dave Peterson]

I don't use Access, but if I formatted the cells (range of cells?) as text, then
copied from Notepad, it stayed text. If it works like that from Access (stays
text), then apply the formula.

Can you convert the date in access into something that is always interpreted as
the date you want. (Jan-03 becomes January 3, (current year) when I
copy|paste.)

By the way, Random asked How--not if.

 

[Adrian Key]

Try this: =TEXT(DATEVALUE(A1),"mmm-yy")
A1=date as text

HTH.