SOLVED

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

Copper Contributor

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 Hans Vogelaar (MVP)
Solution

Hi @CliveHerbert 

 

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:

 

Sample.png

 

in C2:

=SORTBY(
  A2:A7,
  IF(LEN(A2:A7) = 4, DATE(A2:A7,1,1), A2:A7)
)
Hi I am trying to help Clive Herbert but I don’t understand where to enter the formula

@TKing530 

 

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?

IMG_7978.png

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

@TKing530 

 

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

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.
you mean a pivot table would me more helpful right
I think ideally yyyy would be better. This will be used a source data to be imported into MapMate, a biological recording software.

@TKing530 

 

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:

 

Sample.png

where formula in B3 is:

 

=LET(
  date_col,   XMATCH("date",Table1[#Headers]),
  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)
)

 

@TKing530 

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?

@TKing530 

 

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

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

@TKing530 

 

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

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.
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 Hans Vogelaar (MVP)
Solution

Hi @CliveHerbert 

 

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:

 

Sample.png

 

in C2:

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

View solution in original post