Forum Discussion
Sorting Dates which include some before 1900
I am sorting Ancestry information that includes Birth dates. The dates sort correctly if they are newer than 1900. The dates that are older than 1900 aren't sorted. Marriage and Death dates have the same problem. What can I do to sort them without manually modifying them?
PS. what is the SAVE button.
Hi
For exemple, the dates are in A2:A6, 1 or -1 to choose order
=LAMBDA(Plage,Ordre,LET(date1,TEXT(Plage,"dd/mm/yyyy"),date2,TEXT(SORT((TEXTBEFORE(date1,"/",2)&"/"&TEXTAFTER(date1,"/",2)+5000)*1,,Ordre),"dd/mm/yyyy"),TEXTBEFORE(date2,"/",2)&"/"&TEXTAFTER(date2,"/",2)-5000))(A2:A6,-1)- I have 11000 records with 5000 dates before 1900. I understand you to say split the dates of the older dates into 3 columns (Year, Month, Day). I can then sort the three columns, but won't I have the same problem with all the Years before 1900? Some of my dates are just the Year.
In the formulas used to split the date, set Month and Day to 1 if there is only a year.
Excel does not support dates before 1900, so it treats them as text.
Your best bet might be to create three extra columns with year, month and day.
You can use the ISTEXT function to distinguish between dates before and after 1900.
For dates before 1900, i.e. text values, you have to split the text into its parts.
For dates after 1900 you can use the YEAR, MONTH and DAY functions.
- Thanks for your reply. I found the solution. Click right/format cell/ date.
I changed the date format. Finally resolved.
