Sort dates

[K]

I have an Excel list of dates that vary in their format
and include
EU style - dd mm yyyy
US style - mm dd yyyy
some have time too - 02/20/2002 09:22:52:953
I want to produce a standard output (mm dd yyyy) and sort
the list.
Changing the formatting using Format Cells Number Date (or
custom) does not work. The cells are currently formatted
using this method but the output in the cells has not
changed.
Can anyone help me?
Thks
K

 

[Frank Kabel]

Hi
sounds like your dates are actually not stored as dates.
Try the following:
- copy an empty cell
- select your dates
- goto 'Edit - Paste Special' and choose 'Add'

no try changing the format again. This procedure should
convert the text values to real date values

 

[K]

Didn't work (

-----Original Message-----
Hi
sounds like your dates are actually not stored as dates.
Try the following:
- copy an empty cell
- select your dates
- goto 'Edit - Paste Special' and choose 'Add'

no try changing the format again. This procedure should
convert the text values to real date values


.

 

[Peo Sjoblom]

Run this macro on your "dates"

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Domenic]

Hi,

I'm not sure if there's a better way, but try using a helper column.
Enter this formula and copy down:

=IF(ISTEXT(A1),DATEVALUE(MID(A1,FIND("/",A1)+1,2)&"/"&LEFT(A1,2)&"/"&RIGH
T(A1,4)),A1)

Then, select the new column, Copy > Paste Special > Values, and delete
the old column.

Hope this helps!

 

[Domenic]

Sorry, I didn't notice the "953" attached to the timevalue. Provided
that the text entries all have the same number of digits, whether
month/day/year (02/20/2002 09:22:52:953) or day/month/year (20/02/2002),
try,

=IF(ISTEXT(A1),IF(ISNUMBER(FIND(":",A1)),DATEVALUE(LEFT(A1,2)&"/"&MID(A1,
FIND("/",A1)+1,2)&"/"&MID(A1,FIND("/",A1)+4,4)),IF(ISTEXT(A1),DATEVALUE(M
ID(A1,FIND("/",A1)+1,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4)))),A1)

It's not pretty, but hopefully this helps!