Sorting Dates in format "01/19/09"

  • Thread starter Thread starter SCH
  • Start date Start date
S

SCH

How do I set my Excel to Always sort dates in the following format by Year,
Month, and Day? My current setting sorts by Month, date, and then year.
For example my setting is sorting as follows:
01/19/09
02/01/08
02/17/08
etc.
 
By the looks of it, you're sorting as text, rather than dates. What
do=ISTEXT(cellref) and =ISNUMBER(cellref) give you for a typical cell in
your range? If you've got text, you'll need to change to dates before
sorting.
 
By the looks of it, you're sorting as text, rather than dates. What
do=ISTEXT(cellref) and =ISNUMBER(cellref) give you for a typical cell in
your range? If you've got text, you'll need to change to dates before
sorting.
 
Hello SCH,

dates are numbers in excel and it does not deppend the format you use, it
looks you are working with text, instead of date, if my understand is wrong,
and you have dates as dates not as text, you just need sort descending

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SCH" escreveu:
 
Hello SCH,

dates are numbers in excel and it does not deppend the format you use, it
looks you are working with text, instead of date, if my understand is wrong,
and you have dates as dates not as text, you just need sort descending

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SCH" escreveu:
 
If the dates are set as text, you may see green triangles on the top of the
cells. Highlight all of them, click on the yellow exlamation point that
appears, and perform you correction there.
 
If the dates are set as text, you may see green triangles on the top of the
cells. Highlight all of them, click on the yellow exlamation point that
appears, and perform you correction there.
 
How do I set my Excel to Always sort dates in the following format by Year,
Month, and Day? My current setting sorts by Month, date, and then year.
For example my setting is sorting as follows:
01/19/09
02/01/08
02/17/08
etc.
Click to expand...

Most likely your data is stored as TEXT rather than as dates.

To prove this, =ISNUMBER(cell_ref) will give FALSE (if they are stored as dates
it should return TRUE).

To correct this, you can try:
select a blank cell
edit/copy
select your range of dates
edit/paste_special/add

The values may change to 5 digit numbers. Just select to format them as dates.

Now they should sort.

If this does not work, there may be extra characters within the cell.
--ron
 
How do I set my Excel to Always sort dates in the following format by Year,
Month, and Day? My current setting sorts by Month, date, and then year.
For example my setting is sorting as follows:
01/19/09
02/01/08
02/17/08
etc.
Click to expand...

Most likely your data is stored as TEXT rather than as dates.

To prove this, =ISNUMBER(cell_ref) will give FALSE (if they are stored as dates
it should return TRUE).

To correct this, you can try:
select a blank cell
edit/copy
select your range of dates
edit/paste_special/add

The values may change to 5 digit numbers. Just select to format them as dates.

Now they should sort.

If this does not work, there may be extra characters within the cell.
--ron
 
Back
Top