How do I sort by date, properly, in Excel 2007?

[SubjectiveEffect]

I've a spreadsheet that has date in d/m/y as the first column and I need to sort to the sheet by date.

The first column format is DD/MM/YYYY and I've made sure of this on the whole column and on some of the cells that seem to be misbehaving.

The problem is this - when I select the entire sheet and try to sort by date it seems to work but if I scroll down the sheet I can see that there are some 2010 dates mixed in with the 2011 dates.

Therefore I suspect that the sort by "oldest to newest" is doing it by the date the data was entered, which admittedly is not in order.

So how do I sort it all by this column? I've tried selecting in different ways but no luck.

 

[SkipVought]

hi,

Do you really have dates? Dates are just NUMBERS. If you change the CELL FORMAT to GENERAL, a real date will display a NUMBER. If you have real dates, the dd/mm/yyyy is just a DISPLAY FORMAT. The NUMBER is what is used.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

To continue with Skip's train of thought...

Right Click on column A and select Format Cells
Under the Number tab, select General

If the values in column A do NOT change to a weird number (e.g., 1/1/2001 will change to 36892), you currently have Text in the cell, not a date. Check in the cells to see if there are any " or ' in the forumla bar and change them into an actual date (e.g., 1/15/2005). Also, make certain that the dates are correct (e.g., 6/31/2009 is not a valid date)

Once you've corrected the cells, go back and right click on column A and select format cells. Under the Number tab, Select Date and all of the numbers should revert back to dates.

 

[SkipVought]

Once you've corrected the cells, go back and right click on column A and select format cells. Under the Number tab, Select Date and all of the numbers should revert back to dates.

[red]
FORMATTING changes NOTHING![/red]

You cannot change text to a number by merely changing the format.

The TEXT-date must be CONVERTED to a number. You must be careful doing this, as in this particular case, the TEXT-dates seem to be structured as dd/mm/yyyy. Excel ASSUMES mm/dd/yyyy!

I would select the column and use Data > Text to columns using the DMY Column data format


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

Try doing the following to change your dd/mm/yyyy data to a date that Excel can recognize

For data with the date in cell B21

=DATEVALUE(CONCATENATE(IF(MID(B21,5,1)="/",MID(B21,4,1),MID(B21,4,2)),"/",IF(MID(B21,2,1)="/",LEFT(B21,1),LEFT(B21,2)),"/",RIGHT(B21,4)))

the reason for all of the IF commands is in case the data was entered like this 6/2/2010 vs 06/02/2010. This code will recognize both and convert to a date. Note, you may need to change the Number format to see a date vs a raw number.

 

[SkipVought]

This would be simpler...
[tt]
=DATE(RIGHT(B21,4),MID(B21,4,2),LEFT(B21,2))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

A few tweeks to your code to take into accounts when a preceding 0 may be dropped for the day or month:
=DATE(RIGHT(B21,4),MID(B21,FIND("/",B21)+1,+FIND("/",B21,FIND("/",B21)-1)-FIND("/",B21)+1),LEFT(B21,FIND("/",B21,FIND("/",B21)-1)-1))
OR
=DATE(RIGHT(B21,4),(IF(MID(B21,5,1)="/",MID(B21,4,1),IF(MID(B21,4,1)="/",MID(B21,5,1),MID(B21,4,2)))),(IF(MID(B21,2,1)="/",LEFT(B21,1),LEFT(B21,2))))

 

[SkipVought]

the formula assumed the stated dd/mm/yyyy structure.

Actually I'd be apt to use Data > Text to columns - DMY format in place. Much quicker!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

Excel ASSUMES mm/dd/yyyy

Are you sure? Doesn't it look at Windows regional settings and make its assumption based on that?

 

[SkipVought]

I believe. it's the 'Bill Gates lives in Washington, USA' syndrome.

Your default display may conform with regional settings, but string conversion is a horse of a different color - Bill's choice.

But i could be wrong.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

Here's another thing to consider, for those values that Excel thinks is a correct date(e.g., June 1, 2010 would look like 1/6/2010 which Excel would think is January 6, 2010) my formnulae would need to be modified to the following

=if(b21>0, DATE(YEAR(B21),DAY(B21),MONTH(B21)), <Either Code Listed Above>)

 

[SkipVought]

Which is why I stated that I'd use Data > Text to columns - DMY in this instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

You're absolutely right about the Data to Text. I wasn't following exactly what you were meaning, but it's a lot faster (and simplier) than what I was trying to do

I didn't realize that Excel had this built-in function (I thought you were separating the "dates" into 3 columns with the Text to Columns feature and then later recombining them in another cell.)

 

[SkipVought]

I cannot take credit for discovering this trick.

It might have been GlennUK, combo, xlhelp or xlbo.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

Data > Text to columns - DMY format in place.

Ok, I'm missing something.

I see the Date format option button, but it still separates things into three columns.

There is a way to get it all to happen in the original column?

 

[mintjulep]

I'm still on 2003. Is it something introduced after that?

 

[SubjectiveEffect]

Thanks guys.

You've answered above and beyond what I could have asked for and the problem is all sorted out.

Thanks again.

 

[SkipVought]

mint,

Select the data

Data > Text to columns

DO NOT SELECT A DELIMITER >> NEXT

Choose Appropriate DATE format

FINISH

Results in SAME cell/column

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

Neat.