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

Silver Contributor

Dec 30, 2023

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

Copper Contributor

Dec 30, 2023

Hi Lz

Thank you

Forum Discussion