Results 1 to 8 of 8

Thread: Dates Before 1900 Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dates Before 1900 Formula

    I have the formula below that each part works on its own but when I put it all together I keep getting a message about too manny arguments. I googled everthing I could find and nothing works for what I am trying to do. All the formulas I have found do work on Window Excel but not Mac excel. I have a list of events that include birthdays and deceased days among others. I am trying to get the age at death. Only a few dates are before 1900. I can easily do dates after 1900, but not the ones before (not in this If Statement). Thanks again in advance for any help that comes my way

    Birthdays are in column C and the day of death is in column R.

    Formula:

    =IF(C5="","",IF(R5<>"",IF(AND(C5>=0,C5<=2958465),DATEDIF(C5,R5,"Y"),"Approx "&RIGHT(TEXT(R5,"DD MMM YYYY"),4)-RIGHT(TEXT(C5,"DD MMM YYYY"),4),DATEIF(C5,TODAY(),"Y")))

    Forula By If Statements:

    =IF(C5="",
    "",
    IF(R5<>"",
    IF(AND(C5>=0,C5<=2958465),
    DATEDIF(C5,R5,"Y"),
    "Approx "&RIGHT(TEXT(R5,"DD MMM YYYY"),4)-RIGHT(TEXT(C5,"DD MMM YYYY"),4),
    DATEIF(C5,TODAY(),"Y")))

    Hope this helps make it a little clearer.

  2. #2
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,437
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need Help With Dates Before 1900 Formula

    By default, Excel supports only dates after 12/31/1899.

    There are add-ins that claim to support earlier dates. I hope they do that using the default date option. But I have no experience with them

    You could set the "1904 data system" option. In Excel 2010, click File > Options > Advanced and scroll down to checkmark the option.

    Another benefit of setting that option is: negative time can be displayed without converting it to text.

    I do not recommend that, however.

    First, if you already entered dates before selecting the option, the old dates will appear to be changed. For example, 9/18/2019 is changed to 9/19/2023. That's simply a change in the interpretation of the so-called "serial number" (43726). Since that remains unchanged, the appearance is reversible by deselecting the option.

    Second, if you compare dates entered with 1904 option selected to dates without the option selection (e.g. in another workbook), they will not compare "equal", even though they appear to be the same. That is because the "serial numbers" are different. With the 1904 option selected, 9/18/2019 is 42264.

    The 1904 option was intended for using files that are created on or for the Mac.
    Last edited by joeu2004; Sep 18th, 2019 at 04:57 PM.

  3. #3
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help With Dates Before 1900 Formula

    Thank yoy I will try the 1904 trick. I tried to use the add-ins but they would not work on the Mac. They did work on the windows version of excel. Thanks again,[.

  4. #4
    Board Regular Taul's Avatar
    Join Date
    Oct 2004
    Location
    Uxbridge
    Posts
    639
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help With Dates Before 1900 Formula

    Hi,
    This may be of some help for dates prior to 1900
    http://www.exceluser.com/formulas/earlydates.htm
    Excel 2010 on Win7 at home - 2013 on win7 at work

  5. #5
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help With Dates Before 1900 Formula

    Thank you. The formulas on that web site or in my formula. I just don't understand the too many argument errors message I keep getting.

  6. #6
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,437
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need Help With Dates Before 1900 Formula

    Quote Originally Posted by anwaee2 View Post
    I will try the 1904 trick. I tried to use the add-ins but they would not work on the Mac
    Hmm, if you are using a Mac, I would think that the 1904 option is enabled already. Or did you receive the file from a Windows user?

    Anyway, yes, the 1904 option is entirely appropriate to use on a Mac, AFAIK. Let us know if that solves the problem.

  7. #7
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help With Dates Before 1900 Formula

    Thank you, I did try turning on the 1904 system as it was not on. That didn't work either. I still get the too many arguments error message. Here is a revised formula that won't work either. Same message.

    Revised: I have made it shorter and simpler trying to eliminate one If Statement. I just don't see why I get the error message. I see two if statements with three arguments each.

    Formula:
    =iF(AND(C5<>"",R5<>""),IF(AND(C5>=0,C5<=2958465),"Approx "&(RIGHT(TEXT(R5,"DD MMM YYYY"),4))-(RIGHT(TEXT(C5,"DD MMM YYYY"),4)),DATEDIF(C5,R5,"Y"),DATEDIF(C5,TODAY(),"Y")))


    Same Formula Broken Down By If Statements:
    =iF(AND(C5<>"",R5<>""),
    IF(AND(C5>=0,C5<=2958465),
    "Approx "&RIGHT(TEXT(R5,"DD MMM YYYY"),4)-RIGHT(TEXT(C5,"DD MMM YYYY"),4),
    DATEDIF(C5,R5,"Y"),
    DATEDIF(C5,TODAY(),"Y")))

  8. #8
    Board Regular
    Join Date
    Sep 2012
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help With Dates Before 1900 Formula

    Soved!
    Thanks to all but I found the problem.
    For some reason it found the condition C5<>"" and R5<>"" to be invalid. I changed the formula around to use C5="" and R5="" and now it works.

    =IF(C5="","",IF(R5="",DATEDIF(C5,TODAY(),"Y"),iF(AND(C5>=0,C5<=2958465),DATEDIF(C5,R5,"Y"),"*"&RIGHT(TEXT(R5,"DD MMM YYYY"),4)-RIGHT(TEXT(C5,"DD MMM YYYY"),4))))

    Many thanks again to the MrExcel forum.
    Last edited by anwaee2; Sep 19th, 2019 at 08:52 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •