Sort dates

  • Thread starter Thread starter K
  • Start date Start date
K

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
 
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
 
Didn't work :o(
-----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


.
Click to expand...
 
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!
 
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!
 
Back
Top