Excel: date prior to 1900
Solved
gerard27
Posted messages
63
Status
Member
-
Chy -
Chy -
Good evening,
To create a genealogy file, I need to enter dates prior to 1900. However, when I format the cell as a date, it does not recognize dates like "01/01/1761".
No sorting is possible, for example, sorting occurs on the day of the month, not the full date.
To my knowledge, Excel's calendar starts on 01/01/1900.
Question: Is there a way for me to enter dates prior to 1900?
Best regards
Gérard
To create a genealogy file, I need to enter dates prior to 1900. However, when I format the cell as a date, it does not recognize dates like "01/01/1761".
No sorting is possible, for example, sorting occurs on the day of the month, not the full date.
To my knowledge, Excel's calendar starts on 01/01/1900.
Question: Is there a way for me to enter dates prior to 1900?
Best regards
Gérard
9 answers
Hello
I’m going back to my first idea, which was probably the best.
It doesn’t look nice unless you use a trick.
I assume your dates are in column A and
so you’ll insert a column B that you will then hide
but that you will use for sorting.
If your dates are all entered in the format 01/01/1761
you insert in B1 the formula:
=IF(ISNUMBER(A1),NUM(VALUE(TEXT(A1,"yyyymmdd"))),NUM(MID(A1,7,4)&MID(A1,4,2)&MID(A1,1,2)))
If your dates are entered in the format 1/1/1761, the formula is:
=IF(ISNUMBER(A1),NUM(VALUE(TEXT(A1,"yyyymmdd"))),NUM(VALUE(TEXT(MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,4),"0000")&TEXT(MID(A1,FIND("/",A1,1)+1,(FIND("/",A1,FIND("/",A1,1)+1)-FIND("/",A1,1))-1),"00")&TEXT(LEFT(A1,FIND("/",A1,1)-1),"00"),"00000000")))
Then just drag the formula down the entire column.
To do this, select the cell, then position the mouse over
the bottom right corner until you get a small cross and drag
down to the end of the column, and the copy will be made upon releasing.
I hope this will be helpful.
--
always calm
I’m going back to my first idea, which was probably the best.
It doesn’t look nice unless you use a trick.
I assume your dates are in column A and
so you’ll insert a column B that you will then hide
but that you will use for sorting.
If your dates are all entered in the format 01/01/1761
you insert in B1 the formula:
=IF(ISNUMBER(A1),NUM(VALUE(TEXT(A1,"yyyymmdd"))),NUM(MID(A1,7,4)&MID(A1,4,2)&MID(A1,1,2)))
If your dates are entered in the format 1/1/1761, the formula is:
=IF(ISNUMBER(A1),NUM(VALUE(TEXT(A1,"yyyymmdd"))),NUM(VALUE(TEXT(MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,4),"0000")&TEXT(MID(A1,FIND("/",A1,1)+1,(FIND("/",A1,FIND("/",A1,1)+1)-FIND("/",A1,1))-1),"00")&TEXT(LEFT(A1,FIND("/",A1,1)-1),"00"),"00000000")))
Then just drag the formula down the entire column.
To do this, select the cell, then position the mouse over
the bottom right corner until you get a small cross and drag
down to the end of the column, and the copy will be made upon releasing.
I hope this will be helpful.
--
always calm
Hello
You are absolutely right, Excel isn't very fond of genealogy.
To sort dates the way you need,
I can only see one solution and it's the one that was used
before date formats were implemented.
For that, you need to format the dates as yyyy/mm/dd
and in text zone, 1761/01/01 can be sorted with 2005/01/01.
It's not pretty, but if you want to use Excel...
--
still zen
You are absolutely right, Excel isn't very fond of genealogy.
To sort dates the way you need,
I can only see one solution and it's the one that was used
before date formats were implemented.
For that, you need to format the dates as yyyy/mm/dd
and in text zone, 1761/01/01 can be sorted with 2005/01/01.
It's not pretty, but if you want to use Excel...
--
still zen
Hello
I need to seriously look into this!!
It's so vast that we only ever know a part
of Excel: it's the part we use!
If the formulas work for you, I'm glad to have helped
and indeed, onto the next topic.
--
always zen
I need to seriously look into this!!
It's so vast that we only ever know a part
of Excel: it's the part we use!
If the formulas work for you, I'm glad to have helped
and indeed, onto the next topic.
--
always zen
Good evening,
I just tried your suggestion, it works, I can sort by date but as you said, it's not pretty and we're not used to reading dates like that.
Thank you, but I will stick with what I had started doing: one column shows only the day of the month and the month, and another notes the year. For sorting, I sort first by day and month, and second by year, so everything is in order.
I will keep my request active for a few more days to see if anyone has another solution.
Thanks again
best regards
Gérard
I just tried your suggestion, it works, I can sort by date but as you said, it's not pretty and we're not used to reading dates like that.
Thank you, but I will stick with what I had started doing: one column shows only the day of the month and the month, and another notes the year. For sorting, I sort first by day and month, and second by year, so everything is in order.
I will keep my request active for a few more days to see if anyone has another solution.
Thanks again
best regards
Gérard
hello
I've found a solution for you and it's here:
http://www.excelabo.net/xl/dates.php#dates1900
hoping it will work for you...
--
always zen
I've found a solution for you and it's here:
http://www.excelabo.net/xl/dates.php#dates1900
hoping it will work for you...
--
always zen
Good evening, gbinforme
I went to the address indicated, I get to the "EXCLABO" page but I can't go any further. I will try again later.
Gérard
I went to the address indicated, I get to the "EXCLABO" page but I can't go any further. I will try again later.
Gérard
you need to give the page time to load
there you go:
http://j-walk.com/ss/excel/files/xdatedl.php.
which installs as an add-in macro
--
(_Usul of Arrakis
Serial_Dreamer_)
there you go:
http://j-walk.com/ss/excel/files/xdatedl.php.
which installs as an add-in macro
--
(_Usul of Arrakis
Serial_Dreamer_)
hello
I'm sorry the site is unresponsive but I keep looking
and when you search, you find... so see you later
--
always calm
I'm sorry the site is unresponsive but I keep looking
and when you search, you find... so see you later
--
always calm
Hello gbinforme
As you saw, the page is inaccessible.
I just tried your formulas and they work wonderfully. It's exactly what I needed, thank you for your help. I often use Excel but I'm not familiar with the functions apart from "IF ...".
I really need to dive into this!
I'm going to consider the issue resolved.
Thank you again, until the next question or answer.
Best regards
Gérard
As you saw, the page is inaccessible.
I just tried your formulas and they work wonderfully. It's exactly what I needed, thank you for your help. I often use Excel but I'm not familiar with the functions apart from "IF ...".
I really need to dive into this!
I'm going to consider the issue resolved.
Thank you again, until the next question or answer.
Best regards
Gérard
try your internet search using
extended date function
it is a function developed by John Walkenbach that allows the manipulation of dates from the year 100 to 9999, which enables in-depth research on ancestors and provides a long-lasting legacy for descendants. To be brief, this add-in can be found at this address
http://j-walk.com/ss/excel/files/xdate.htm
Best regards
G. David
--
Respect is not a given
it is earned