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

[CliveHerbert]

How can I:

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

[#2 · Accepted 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:

in C2:

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

[#3]

Hi Lz

Thank you

[#4]

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

[#5]

@TKing530 

Wherever you want the result.

[#6]

@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?

[#7]

Yes let me do that

[#8]

 

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

[#9]

@L z

[#10]

@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

[#11]

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.

[#12]

you mean a pivot table would me more helpful right

[#13]

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

[#14]

@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:

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)
)

[#15]

@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?

[#16]

@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

[#17]

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

[#18]

@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

[#19]

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.

[#20]

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