sorting by date

  • Thread starter Thread starter AB
  • Start date Start date
A

AB

Hi List, I want to sort by date, but its doing it by the day not the year.
eg putting 1/2/1850 before 2/2/1849. How do you make it sort by year? Or
would I need to put year in a separate column?
Thanks
Althea
 
Excel date system ends (or rather starts) on Jan 0, 1900
your dates are seen as text, it would definitely be easier if you used
multiple
columns..
 
Hi Althea!

If all your dates are before 1900, then they are being viewed as text
by Excel which only allows dates back to 1-Jan-1900

You could set up a duplicate helper column and then apply Data > Text
to Columns on the Helper column using the / as the delimiter. That
would give you columns for day, month and years and you could sort
those in year > month > day order.

Alternatively, you could set up a helper column and parse the date
entries using:

=--RIGHT(A1,4)*10000+IF(ISNUMBER(--MID(A1,FIND("/",A1)+1,2)),--MID(A1,
FIND("/",A1)+1,2),--MID(A1,FIND("/",A1)+1,1))*100+IF(ISNUMBER(--LEFT(A
1,2)),--LEFT(A1,2),--LEFT(A1,1))

This will give a list of numbers such as 18500112 for 12 Jan 1850 and
you can sort those numbers to get the right order. In passing the
format of this number yyyymmdd is the ISO8601:2000 non-separated
agreed form and the sort capability was probably one reason why it was
selected. Many computer programmers have used this form of date
numbering for years.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
The cells are probably formatted as 'text' not 'date'.

How many dates do you have?

If it is just a few -
insert a column (alongside)
format it to a date format
re-type the dates.

If there are a lot of dates (?tens) -
insert a column (alongside)
format it to a date format
type in the formula =VALUE(A1)
copy the formula down
highlight the column
click on <Edit><Copy><Edit><Paste Special><Values><OK>
delete the column with the original

Regards.

Bill Ridgeway
Computer Solutions
 
Lucky you! Mine did do the year first in order but I would like to sort by month first. Any ideas?
 
Hi Suzanne!

As long as the dates are all dates.

Add helper columns

=MONTH(A1)
=YEAR(A1)
=DAY(A1)

Sort using those columns as criteria to taste.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Suzanne said:
Lucky you! Mine did do the year first in order but I would like to
Click to expand...
sort by month first. Any ideas?
 
Back
Top