View original (French)

Excel: date prior to 1900

Solved
gerard27 Posted messages 63 Status Member -  
 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

9 answers

  1.
    gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    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
    11
    1. G.David Posted messages 790 Status Member 203
       
      Hello
      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
      0
    2. Chy
       
      Impeccable, it works like a charm.
      0
  2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    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
    2
  3. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    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
    1
  4. gerard27 Posted messages 63 Status Member 5
     
    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
    0
  7. gerard27 Posted messages 63 Status Member 5
     
    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
    0
  9. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    hello

    I'm sorry the site is unresponsive but I keep looking
    and when you search, you find... so see you later

    --
    always calm
    0
  10. gerard27 Posted messages 63 Status Member 5
     
    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
    0