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

aikox2

New Member
Joined
Oct 27, 2005
Messages
2
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,779
Messages
6,121,520
Members
449,037
Latest member
tmmotairi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top