Date format

[Jessie Williams]

Hi all, I don't know why I can't seem to work this out! I want to have a column with dates entered and then be able to data sort from earliest to latest date (by month). What is the best way to do this?

Many many thanks!

jess

[Kim_J]

You can go to the Data option on the menu bar and choose sort

Then choose to sort ascending.

Is that what you are looking for?

[Jessie Williams]

Thanks Kym - I do know that command but it's not working for ordering dates. Some dates are formatted (I assume that's what the green colour in the cell means!) so I would have thought doing a generic alpha sort , the months would be sorted in ascending order, but there doesn't seem to be any rhyme or reason to the sort.

Jess

[Kim_J]

It might work better if all of the dates were formatted the same way? Highlight the column, then right-click and choose format. select dates and choose one date format.

Hopefully that will work better.

[Richard Buttrey]

Thanks Kym - I do know that command but it's not working for ordering dates. Some dates are formatted (I assume that's what the green colour in the cell means!) so I would have thought doing a generic alpha sort , the months would be sorted in ascending order, but there doesn't seem to be any rhyme or reason to the sort.

Jess

Are the dates you're seeing derived from a date 'number', for example does the 29th March 2008 show as date number 39536?

If your 'dates' are not sorting as you expect then that's probably because some are just text which look like dates. The important thing to remember is that formatting has no effect on the underlying information. Formatting is just there to make things display in the way you want them to look. If a cell has text which happens to look like a number or date, no amount of formatting will make it behave like a number.

Rgds

[Jessie Williams]

It's beginning to make sens to me Richard thanks! Yes they are text that look like dates. What is the best way to input a whole list of dates to be able to sort them? Is that possible?

TIA

Jess

[Richard Buttrey]

It's beginning to make sens to me Richard thanks! Yes they are text that look like dates. What is the best way to input a whole list of dates to be able to sort them? Is that possible?

TIA

Jess

Hi,

Just enter the date in the format dd/mm/yy, or if you're in the US probably mm/dd/yy -

i.e. 30/3/08 or 3/30/08 or 3/30/2008 if you want the whole year.

You can always check that they are date 'numbers' by using the ISNUMBER(A1) function in another cell - obviously change the A1 reference to the column and cells in which you're entering dates.

Once they are date numbers, you can of course format them to display exactly as you want. e.g. 30 Mar 2008, 30 March 2008 etc..
The formatting doesn't alter the number or the subsequent sorting.

HTH

[Jessie Williams]

Lovely lovely thank you!