Dates Before 1900 Formula

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
151
Office Version
  1. 2011
Platform
  1. MacOS
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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:
Upvote 0
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,[.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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")))
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top