Sort Date column when month and day are unknown

[abarbee314]

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. 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!

Name	Date
Burras	10/24/2018
Robinson	'2003/00/00
Plaintiffs	'2009/00/00
Robinson	'2010/00/00

<tbody>
</tbody>

 

[alansidman]

Would it be acceptable that the unusual dates are changed to ie. 1/1/2003 for each year?

 

[Tetra201]

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.

 

[abarbee314]

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.

 

[abarbee314]

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.