Sorting dates from 1800's to 1900's
- Thread starter seansr
- Start date
NARAYANK991
Excel Ninja
Hi ,
You might find this link useful :
http://chandoo.org/forums/topic/how-to-work-on-dates-before-1900-in-excel-without-using-vba-codes
Excel does not have native functions to deal with pre-1900 dates. You will have to convert the post-1900 dates to text ( so that the pre-1900 dates which are already text , since Excel does not recognize them as valid dates , and the post-1900 dates converted to text , can all be formatted as yyyymmdd ) , or use Walkenbach's add-in to process all of them together.
Narayan
You might find this link useful :
http://chandoo.org/forums/topic/how-to-work-on-dates-before-1900-in-excel-without-using-vba-codes
Excel does not have native functions to deal with pre-1900 dates. You will have to convert the post-1900 dates to text ( so that the pre-1900 dates which are already text , since Excel does not recognize them as valid dates , and the post-1900 dates converted to text , can all be formatted as yyyymmdd ) , or use Walkenbach's add-in to process all of them together.
Narayan
Faseeh
Excel Ninja
Hi Seansr,
I have assumed following date pattren YYYY/DD/MM the date are in column A like below:
[pre]
here is the sample file:
http://dl.dropbox.com/u/60644346/Pree1800DateSolution.xlsx
Let me know if your date format is different.
Regards,
Faseeh
I have assumed following date pattren YYYY/DD/MM the date are in column A like below:
[pre]
Code:
1993/7/15
1993/7/14
1993/8/12
1993/12/11
1893/7/11
1893/7/10
1893/7/9
1893/7/8
1893/7/7
1893/7/6
1893/7/5[/pre]
Enter this formula in B2 and drag down, then sort for column B.
=DATE(LEFT(A2,4)+100,TRIM(SUBSTITUTE(RIGHT(A2,2),"/","")),TRIM(SUBSTITUTE(MID(A2,5,3),"/","")))here is the sample file:
http://dl.dropbox.com/u/60644346/Pree1800DateSolution.xlsx
Let me know if your date format is different.
Regards,
Faseeh
Copy of data
Please note I am not a wiredo - it is a transcrip of a register for the local family history society
https://www.dropbox.com/s/91cgg8vf9rkph9q/Copy%20of%20health%20authority%20Death%20Registers%20inputting%20%20formulas%20sean.xls?m
What I am wanting to do is to sort the data so that it can be worked on in date order but returned in alpahbetical order for release to the public.
Those rows not highlighted had a sort column typed manually
those highlighted I created a formular to show yyyymmdd
If you can help sort it from the earliest date to the latest date.
If you can let me know step by step I can apply it to the full data
Many thanks
Please note I am not a wiredo - it is a transcrip of a register for the local family history society
https://www.dropbox.com/s/91cgg8vf9rkph9q/Copy%20of%20health%20authority%20Death%20Registers%20inputting%20%20formulas%20sean.xls?m
What I am wanting to do is to sort the data so that it can be worked on in date order but returned in alpahbetical order for release to the public.
Those rows not highlighted had a sort column typed manually
those highlighted I created a formular to show yyyymmdd
If you can help sort it from the earliest date to the latest date.
If you can let me know step by step I can apply it to the full data
Many thanks
NARAYANK991
Excel Ninja
Hi Sean ,
The problem is the data is in all sorts of formats ! You have all of the following :
1. 1/2/1904 , a valid date
2. 02 Jan 1875 , a text string with 2 characters for the date , 3 for the month
3. 8 Feb 1875 , a text string with 1 character for the date
4. 1 Sept 1875 , a text string with 4 characters for the month
5. 2 Feb 1890 , a text string with 1 character for the date , but with a leading space
6. 28 Aug 1893 , a text string with additional spaces between the date , month and the year
7. 9June 1897 , a text string without any space between the date and the month
8. 21st July 1898 , a text string with the date in ordinal fashion
The first step is to clean up this data , so that the format is the same for all entries.
Narayan
The problem is the data is in all sorts of formats ! You have all of the following :
1. 1/2/1904 , a valid date
2. 02 Jan 1875 , a text string with 2 characters for the date , 3 for the month
3. 8 Feb 1875 , a text string with 1 character for the date
4. 1 Sept 1875 , a text string with 4 characters for the month
5. 2 Feb 1890 , a text string with 1 character for the date , but with a leading space
6. 28 Aug 1893 , a text string with additional spaces between the date , month and the year
7. 9June 1897 , a text string without any space between the date and the month
8. 21st July 1898 , a text string with the date in ordinal fashion
The first step is to clean up this data , so that the format is the same for all entries.
Narayan
Is there any way you know to convert the dates into that format, other than manually go through them.
The 1900's are ok, but the 1800 will not convert.
also if I manually convert then I can't get them to sort correctly. The 1900 sort but not the 1800's see below
using these as an example
21/12/1898
12/10/1897
02/12/1898
02/02/1890
01/01/1898
19/07/1933
19/07/1933
19/07/1933
18/07/1933
The 1900's are ok, but the 1800 will not convert.
also if I manually convert then I can't get them to sort correctly. The 1900 sort but not the 1800's see below
using these as an example
21/12/1898
12/10/1897
02/12/1898
02/02/1890
01/01/1898
19/07/1933
19/07/1933
19/07/1933
18/07/1933
NARAYANK991
Excel Ninja
Hi Sean ,
There are two issues in the file :
1. The 'dates' , even if they are not valid Excel dates ( pre-1900 dates ) , should have had a standard format , which they don't have.
This issue has to be tackled first ; it is independent of the fact that they are pre-1900 dates. We will have to use a different formula for each of the 7 problem cases I mentioned in my earlier post , so that all of them are converted to text with a format yyyymmdd.
2. The valid dates ( post-1900 dates) will also have to be converted to text format using the TEXT function.
Once the entire data is in a standard text format , the sort will work correctly.
If there are more problem cases other than the 7 I have listed then , for each one that is found , a different formula will have to be used to convert it to the yyyymmdd text format.
Narayan
There are two issues in the file :
1. The 'dates' , even if they are not valid Excel dates ( pre-1900 dates ) , should have had a standard format , which they don't have.
This issue has to be tackled first ; it is independent of the fact that they are pre-1900 dates. We will have to use a different formula for each of the 7 problem cases I mentioned in my earlier post , so that all of them are converted to text with a format yyyymmdd.
2. The valid dates ( post-1900 dates) will also have to be converted to text format using the TEXT function.
Once the entire data is in a standard text format , the sort will work correctly.
If there are more problem cases other than the 7 I have listed then , for each one that is found , a different formula will have to be used to convert it to the yyyymmdd text format.
Narayan
NARAYANK991
Excel Ninja
Hi Sean ,
Can you see this file ?
http://speedy.sh/Jhd7Y/health-authority-Death-Registers-sean.xlsx
Only about 250 cases still remain to be resolved ; can you look into them ? Otherwise , the rest of the data is already sorted on column R.
Narayan
Can you see this file ?
http://speedy.sh/Jhd7Y/health-authority-Death-Registers-sean.xlsx
Only about 250 cases still remain to be resolved ; can you look into them ? Otherwise , the rest of the data is already sorted on column R.
Narayan