Dates Before 1900 Formula

[anwaee2]

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.

 

[joeu2004]

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.

 

[anwaee2]

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,[.

 

[Taul]

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

 

[anwaee2]

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.

 

[joeu2004]

Re: Need Help With Dates Before 1900 Formula

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.

 

[anwaee2]

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")))

 

[anwaee2]

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.