Sort Date column when month and day are unknown

abarbee314

New Member
Joined
Apr 5, 2013
Messages
24
Helping our resource center with a crazy project: an attorney has a table of data where 40% of the date values are like this "2003/00/00" and the rest are US-correct (03/15/2019). This one is hurting my brain, but I can't figure out how to (a) re-arrange the values and (b) get it to sort right amongst the other 2003 values. Or can it?

Bonus bugger: there are several instances where the "2003/00/00" values (again, an example) are preceded by an apostrophe for some reason. Deleted one of them 8 times and it never goes away. :mad: Tried some weird paste values + Add from a blank cell trick, didn't work. Tried text-to-columns with apostrophe as the delimiter (the wizard doesn't see it).

Any help would be certainly appreciated! Very sample provided below

Thanks! :)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Burras[/TD]
[TD]10/24/2018[/TD]
[/TR]
[TR]
[TD]Robinson[/TD]
[TD]'2003/00/00[/TD]
[/TR]
[TR]
[TD]Plaintiffs[/TD]
[TD]'2009/00/00[/TD]
[/TR]
[TR]
[TD]Robinson[/TD]
[TD]'2010/00/00[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Would it be acceptable that the unusual dates are changed to ie. 1/1/2003 for each year?
 
Upvote 0
See if the following approach works for you:

- Create a helper column and populate it with your dates converted into text strings using =TEXT(B2,"yyyy/mm/dd").
- Your ".../00/00" values will appear in the helper column unchanged.
- Now you can sort your data by the helper column.
 
Upvote 0
Hi Alan. Unfortunately, no. adding a 1/1/yyyy would throw off the sorting for something with no known month/date and make it the first item in that year based on presumptions. I asked that too, but I also wondered how Excel would interpret date sorting with the zero's. Maybe it can't and that's why this isn't working well. :(

Thank you, sir.
 
Upvote 0
Thanks Tetra201! I think that just might be the trick we needed. :)

I had to clean up the list I was given to re-write a few that had been entered as "March 2017" and listed them as 2017/03/00. I then ran the text conversion on the whole helper column and it put those 00 dates at the beginning of those years. It's just going to have to work.

Thanks again, folks. :cool:
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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