how do I sort dates which contain unknown field (01/xx/05)

[aikox2]

I maintain a list of concert recordings using a mm/dd/yy format, and I wish to sort in chronological order.

The problem I have is that if I use any non numeric characters instead of a day or month when I do not have the exact date, the date is not sorted as I would have hoped:

02/xx/74 will not appear between 01/20/74 and 03/10/74, but rather at the end of the list.

Is there a way to sort a chronological date list with unknown mm field?

Thank you for any help.

 

[barry houdini]

Assuming your dates are in column A starting at A2 perhaps you can use this formula in another column and sort by that column

=SUBSTITUTE(A2,"xx","01")+0

 

[aikox2]

thanks for the reply.

I tried that, but either I did it wrong or it does not work.

I am no Excel maven, so if you could provide more detailed help, that would be greatly appreciated.

Although I do believe there should be a way to do this, I cannot figure it out. I have tried substituting various characters or using 0 (zero) or 32 for the date value, but none of those will work and place the date in the list either at the beginning or end of the month it should be in.

Again, I am trying to get a date like 01/xx/03 to appear either just before 01/01/03 or before 02/01/03

01/xx/03
01/04/03
01/22/03
02/16/03...

or

01/04/03
01/22/03
01/xx/03
02/16/03

Thanks for any help or suggestions.

Aiko

 

[barry houdini]

I believe it will work to an extent but I'm not sure it will work in exactly the way you require.

It will mean that where you have xx instead of a day then it will be treated as 1st of the month, where you have xx for the month it will be treated as January.