How to sort data by date format

[Emma_Sabin]

Hi - I'm using PC/Windows 10, and Excel version 2018

My excel data looks like this. I have 1500 rows. 

Date of hire	Gender	Race/Ethnicity	Level	Promotion Date	new level	term date	type of term	Region
02/24/2003	Female	White	1	n/a	n/a	n/a	Involuntary	Europe
05/09/2005	Male	African American	2	n/a	n/a	n/a	Voluntary	Asia
10/15/2007	Male	Hispanic	3	n/a	n/a	n/a	n/a	North America

The problem is the date data is in different formats. Some are formatted as general m/d/yy, but some are formatted as custom: d/m/yy. If I change all the dates into one format I end up changing the months to days or vice versa.  I'd like to sort the table by the date format so I can just change one half of the dates to the other half's format. Any ideas? Thanks, Emma 

[#2]

@Emma_Sabin 

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following custom function into the code window:

Function NumberFormat(rng As Range)
    NumberFormat = rng.NumberFormat
End Function

Close the Visual Basic Editor.

In the first column next to the data, enter the following formula in row 2:

=NumberFormat(A2)

Fill down to the last row with data, for example by double-clicking the fill handle in the lower right of the cell with the formula.

Now sort the entire range (not just the new column!) on the new column.

Cells with the same number format will be grouped together.

[#3]

how can you tell which date is formatted which if the date is 12 or less and the month is 12 or less? i.e 12/12/2007? or 9/12/2007? or 12/9/2007? How can you tell which number is the day and which number is the month and how do you decide how to sort them? by month? or by day?

[#4]

@Emma_Sabin You need to convert the text date to an Excel date that can be sorted. (They count the number of days from about 1 jan 1900 and can thus be sorted correctly).

In the example, it is possible to identify the format as M/D/Y but as mentioned in another reply, that is not always the case.

The attached file converts quite a few date formats from text to date.

When ambiguous, it returns multiple alternatives in column B.

For your need, column O should come handy.

Insert an extra column to calculate the Excel date and then use it for sorting your data.