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) )
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?
Lorenzo It is 13 columns of data about sightings of a type of snake. Sometimes dd/mm/yyyy but sometimes 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 https://support.microsoft.com/en-us/office/format-an-excel-table-6789619f-c889-495c-99c2-2f971c0e2370, 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.
- @L z
- Yes let me do that