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) )
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?
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
But trying to do it manually as the typist typed in Google sheets and I think it is what is creating the issue
I didn't pay enough attention to this and now that you've mentioned Google Sheets I reviewed the picture you shared earlier. It would appear that it's not only the yyyy values that are Text values but also the dd/mm/yyyy (they appear left-aligned as well):
So, IF the dd/mm/yyyy values are also Text values a change is required in what I earlier re-shared:
=LET( date_col, XMATCH("date",Table1[#Headers]), array, IF(ISBLANK(Table1), "", Table1), dates, IF(LEN(Table1[Date]) = 4, DATE(Table1[Date],12,31), DATEVALUE(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) )In the attached sample, sheet 'Data', you'll see that I simulated dd/mm/yyyy values as Text values by adding a quote char. before i.e. '10/05/1994
Yes - I will add 31/12/yyyy to all the yyyy only cells then find some way that is does not show. Or to format it into date. Every time I try it messes it up
I really don't understand what you do that messes things up. In the 1st version I posted (attached again), when there only a year value (i.e. 1995) the formula automatically creates date value 31/12/1995 in the SortedData sheet. Though, values in the [Date] column of SortedData sheet must be https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e#:~:text=Follow%20these%20steps%3A%201%20Select%20the%20cells%20you,choose%20the%20language%20in%20Locale%20%28location%29.%20See%20More.
=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) )- Yes - I will add 31/12/yyyy to all the yyyy only cells then find some way that is does not show. Or to format it into date. Every time I try it messes it up. But trying to do it manually as the typist typed in Google sheets and I think it is what is creating the issue/
- Can you please re-share the formula you used?
- Correct. And thanks to the Dynamic Arrays, what's entered in the 'Data' sheet is immediatly sorted in sheet 'DataSorted'. Try it...
- 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. 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