Forum Discussion
Sort by table chronologically in MS Excel when some date are DD/MM/YY and some are YYYY only
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) )
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
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
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) )- I think ideally yyyy would be better. This will be used a source data to be imported into MapMate, a biological recording software.