Sep 18 2021 01:27 PM
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
Sep 18 2021 01:46 PM
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.
Sep 19 2021 04:49 AM
Sep 19 2021 10:55 AM
@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.