SOLVED

# Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

Copper Contributor

# Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

How can I:

Sort by table chronologically by date in MS Excel when some date are DD/MM/YY and some are YYYY only

27 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

In the below example, when there's no dd/MM but YYYY only (length=4) the latter is used to construct a Date like 01/01/YYYY:

in C2:

``````=SORTBY(
A2:A7,
IF(LEN(A2:A7) = 4, DATE(A2:A7,1,1), A2:A7)
)``````

Hi Lz

Thank you

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

Hi I am trying to help Clive Herbert but I don’t understand where to enter the formula

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

Wherever you want the result.

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

You enter the formula where you expect to get the result. That being said this won't necessarily help you as I have no idea where your data are stored on your spreadsheet, how many columns you have...

Could you post at leasr picture showing your spreadsheet?

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

Yes let me do that

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

@Lorenzo It is 13 columns of data about sightings of a type of snake. Sometimes dd/mm/yyyy but sometimes yyyy only.

@L z

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

Thanks. Couple of questions:

#1 In your [Date] column, when there's only yyyy values are left aligned. Does this mean they are Text values or do simply left aligned the cells?

#2 Given we need to construct a Date value when there's only yyyy, in the result (sorted) do you only want to see yyyy or would 01/01/yyyy be acceptable?

In the meantime, please take a look at how to format as a Table, this will make your life easier

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

Yes the typist typed the data as text, because of the issue that some entries only have yyyy and not dd/mm/yyyy she could not format it as a date.

I guess 31/12/yyyy would be acceptable because the dd/mm/yyyy values should come before yyyy values.

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

you mean a pivot table would me more helpful right

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

I think ideally yyyy would be better. This will be used a source data to be imported into MapMate, a biological recording software.

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

you mean a pivot table would me more helpful right

Nope. If you open the link I shared earlier it explains how to Format a dataset as a Table, something I've done is the attached workbook in sheet 'data' (table is named Table1). Then on sheet 'DataSorted:

where formula in B3 is:

``````=LET(
array,      IF(ISBLANK(Table1), "", Table1),
dates,      IF(LEN(Table1[Date]) = 4, DATE(Table1[Date],12,31), Table1[Date]),
repl_dates, HSTACK(
CHOOSECOLS(array, SEQUENCE(,date_col-1)),
dates,
CHOOSECOLS(array, SEQUENCE(,COLUMNS(Table1)-date_col,date_col+1))
),
SORT(repl_dates, date_col)
)``````

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

I think ideally yyyy would be better. This will be used a source data to be imported into MapMate, a biological recording software

This comes a bit late & what difference would this make given you have dd/mm/yyyy values in the [Date] column anyway?

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

The attached file is the same as the previous one but it keeps the yyyy values as-is instead of converting them to 31/12/yyyy

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

So with the above formula assuming the dataset is formatted as a Table, the entries can be sorted chronologically?

I think the difference would be that saying 31/12/1982 will export an incorrect date into the new software, where as just 1982 is accurate..

How can a person hire someone to do this is way beyond anything I can understand

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

So with the above formula assuming the dataset is formatted as a Table, the entries can be sorted chronologically?

Yes, see sheet 'DataSorted'

I think the difference would be that saying 31/12/1982 will export an incorrect date into the new software, where as just 1982 is accurate

OK, so see last workbook I shared

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

I've just seen the data sorted - thanks
So they paper version the typist is typing can be in one tab per species and then the second tab can sort the data she has typed.

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

Correct. And thanks to the Dynamic Arrays, what's entered in the 'Data' sheet is immediatly sorted in sheet 'DataSorted'. Try it...
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only

In the below example, when there's no dd/MM but YYYY only (length=4) the latter is used to construct a Date like 01/01/YYYY:

in C2:

``````=SORTBY(
A2:A7,
IF(LEN(A2:A7) = 4, DATE(A2:A7,1,1), A2:A7)
)``````