Trying to sort dates to include a range of dates

Hi there.

I'm trying to categorize some data for work and some of the documents I need to categorize have a single date (ex: 8/24/2017) while others use a range of dates (ex: 8/10/2017 - 8/17/2017).

As of right now, my data is in a table so that I can sort between dates, document title, and document number. But when I sort by date, it doesn't go in chronological order because of the dates that include ranges; these are placed at the end. How can I use the filter to sort dates with ranges in them?

Edit: I tried to attach a sample .xlsx but the forum wouldn't let me. Every time I clicked the attachment button only a blank drop down menu would pop up. I tried making a table using the forum's tools. Hopefully this will be good enough.

Current

Date	Description	Problem
6/30/2014	Normal Date	No Problems
8/12	Unknown Year	The year is unknown but Excel uses current year
6/21/2014 - 7/2/2014	Date is a range	Excel doesn't grab the date (want to use the first date in the range)
7/8	Unknown Year	Note: See Desired table

Desired

Date	Description	Notes
6/21/2014 - 7/2/2014	Date is a range	First date in the range is selected for sorting
6/30/2014	Normal Date	No problems
7/8	Unknown Year	Excel puts entries with unknown years at the end
8/12	Unknown Year	Excel sorts entries with unknown years by mm/dd

Edit 2: I'm really close to getting this document done! I came across a new problem though. To get the dates with no years to appear at the end when sorted, I wrote "3000" as the year. Unfortunately, some of the date ranges have an unknown year. I can't format them to just show m/d (ex: 7/12 - 7/30) without Excel automatically changing the year to 2017. Is there a way to format the cells with unknown years to just show m/d and keep the year as "3000"? It's not too big a deal; the document works perfectly fine as is. It would only look nicer if the ranges didn't show the year "3000".

Hi ehh (Canada??), welcome to the forum! You could use the following formula in a separate column (which must be formatted as Date)
Assuming dates in column B, it extracts the first date if a date range is found, else it returns the column B value unchanged.:

Please Login or Register  to view this content.

Paste in row 2 and copy down. Now you can filter on the new column...

You know, a look at some representative data would help us to solve your problem quickly and accurately. The simplest thing would be to upload a sample workbook (NOT a picture). Remove any sensitive or extraneous stuff, but include examples of any "strange" data the formula must deal with ... and perhaps simulate some results to demonstrate what you want.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now just Browse for your file, then click Upload. Simple!

=IFERROR(DATEVALUE(LEFT(B2,SEARCH("? -",B2))),B2)

I tried using leelnich's formula on my table but the formula automatically fills up the whole table. This means that it fills out rows I don't have any data in so when I sort from oldest to newest, there's an infinite amount of rows labeled "1/0/1900". Is there a way I can use this formula without it automatically filling out the entire table?

Edit: Never mind, I figured out that my table size was set for $A:$E. I resized it to only have the number of rows I need and it works perfectly now!