Sorting Date Formats

  • Thread starter Thread starter stan
  • Start date Start date
S

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!
 
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
 
Another way using that same helper column idea:

=DATEVALUE(LEFT(A1,3)&" 1, "&IF(RIGHT(A1,2)>"30",1900,2000)+RIGHT(A1,2)*1)
 
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?
 
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?
 
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?
Click to expand...
 
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.
 
Back
Top