excel and date sorting - please help

[alfio]

hi all

i'm stuck on what (should be) an easy excel task but cannot find the solution.

i've got a series of dates (lots of them) written in individual cells in the following format: day/month/year (i.e. 18/06/1905). i would like to be able to sort them. any ideas? i wouldn't mind switching to the US format (i.e. month/day/year) if it would help with the sorting.

thanks

alfio

[RealBlackStuff]

The only way to sort dates in one go would be: yyyy-mm-dd

[alfio]

The only way to sort dates in one go would be: yyyy-mm-dd

ok, i can live with that - how can i get that done (please don't tell me i need to re-enter all the data)?

(in one of my earlier attempts, i tried getting to the format you suggest by using the format cells option but could not get it to work)

thanks

alfio

[RealBlackStuff]

There's no magic or re-entering required.
Highlight the date fields or column(s).
Select Format>Cells>Custom. In the Type box, enter yyyymmdd or yyyy-mm-dd or yyyy/mm/dd.

Try it out on a copy of your spreadsheet, to make sure you get the result you want.

[Purcy]

RBS, I tried this out on an Excel sheet I have with dates in one column; I actually did the Custom Format as you detailed; but after I clicked OK, it did not RE-format the already existing highlighted dates in the column.

[RealBlackStuff]

Is your spreadsheet perhaps protected? (check Tools/Protection)
This works in any direction you like.

[Purcy]

Is your spreadsheet perhaps protected? (check Tools/Protection)
This works in any direction you like.

No, it is not protected, I checked. I'll fool some more with it, I want to be a pro with Excel.

So, how is Mt. Cobb in the summer? That is a lovely area.

[rkawakami]

I think that the problem stems from the possibility that the original date format has been stored as "General" or as a text string.

@alfio: Are ALL of the dates padded with the appropriate leading zero for both day and month?

edit: Assuming the answer is yes, and that all of the data for the date column is in "text" format, then this function should work:

=DATE(VALUE(RIGHT(A1,4)),VALUE(MID(A1,4,2)),VALUE(LEFT(A1,2)))

In the example above, "A1" refers to the cell which contains the original text string in the format of "DD/MM/YYYY". You would plug this formula into an adjacent column, with the appropriate change to the "A1" cell. Then extend the formula down the column; click and drag the right corner of the first highlighted cell down. This will create a column with the correct DATE values. You can then format the column using Format/Cells/Custom/Type: yyyy-mm-dd. Then you can sort it.

edit2: Here's an example .xls file (stored in Excel 97 format): http://www.kawakami-ca.com/excel/date_test.xls

Actually, you don't really have to format the new date column before sorting. Since the date values are stored as numbers internally, you can sort them without having to format them in any specific way.

[RealBlackStuff]

So, how is Mt. Cobb in the summer? That is a lovely area.

Most of the time quite pleasant, thank you.
Yesterday was a bit hot with 95 oF.
That's when having a pool really comes in handy.

[alfio]

@alfio: Are ALL of the dates padded with the appropriate leading zero for both day and month?

hi ray,

no, i do not have all with leading zeros and i suspect that is the real problem. i have some that are listed d/m/yyyy and most others listed as dd/mm/yyyy. i tried filling them back in but it simply auto-reverts to d/m/yyyy. i guess i need to fix this first, question is how (?). is there a way to avoid the auto-reverting?

that excel is one stubborn mofo.

alfio

[gator]

alfio, you have a PM.

edit: I accidently deleted the post that was below this post of mine. I sincerely apologise for the mistake ... whoever posted it, can you please post it again?

[alfio]

it seems like before anything, i have to undo the US-based date settings (mm-dd-yyyy) because no matter what i do, that always messes everything up

because the dates are formatted dd-mm-yyyy when i change 9/7/1907 with any formatting option it inevitably turns it into 07/09/1907. others which can't be inverted (say 23/04/1959) stay as they should but the potentially ambiguous ones switch and mess everything up.

again, thanks all for your help with this

alfio

[gator]

What you need is a shell script to add the leading zero ... a sed or awk script is pretty easy to write. I sent you an email with details.

[rkawakami]

edit: I accidently deleted the post that was below this post of mine. I sincerely apologise for the mistake ... whoever posted it, can you please post it again?

Uh... that was me !

Basically I said that you need to change each cell's data to a text string by adding a single quote (') at the beginning. Any leading zeros must also be added to the month and day portions for the formula I posted to work correctly.

[RealBlackStuff]

If your Windows system date setting is mm/dd/yy (standard USA)
and you enter a date (e.g. March 15 2008) as 3/15/08,
the field will show as 03/15/08 and it will be right-justified (if not set to middle or left-justify).
If you enter it as 15/3/08 or 15/03/08 it will remain as-is and it is left-justified (because date not recognized as such).

If most (or all) of your dates are entered as dd-mm-yyyy, you should temporarily change your Date format in Control Panel/Regional and Language options.
Click on the Customize button, then Date tab and change short-date-format to dd/M/yyyy to reflect what you have entered.

Go back to your spreadsheet, make a copy (to be sure) and change the layout of dates as per MY format above. Save your work.
When done, change the Regional setting back to what it was.

Check that the (copied) spreadsheat is OK if you sort the data.

[alfio]

hi all

apologies for dropping off the face of the earth on this one - i hate not posting the resolution to a fix.

anyhow, the solution was, as RealBlackStuff suggested, very much connected to the way XP formats dates. i changed the prefences, changed the dates to have the month written out (i.e. 19/09/1942 --> sept 19, 1942), and, as a precaution, saved it all as text on another file. i then made the switch to a US-style date on my system preferences and all was good. thanks again everyone

alfio