Can datevalue convert "April 30, 1789" to a date value?

[JenniferMurphy]

As I read the DateValue explanation, it should convert text dates to numbers. In the mini-sheet below, I am getting a value error when trying to covert the dates I copied from a website.

What am I doing wrong?

President Rankings.xlsx
	B	C	D	E	F	G	H
4	#	Name	Born	Died	Term Start	Term End	Years
5	1	George Washington	1732	1799	April 30, 1789	March 4, 1801	#VALUE!
6	2	John Adams	1735	1826	March 4, 1801	March 4, 1817	#VALUE!
7	3	Thomas Jefferson	1743	1826	March 4, 1817	March 4, 1829	#VALUE!
Sheet2 (2)

Cell Formulas
Range	Formula
H5:H7	H5	=DATEVALUE([@[Term End]]) - DATEVALUE([@[Term Start]])

 

[Michael M]

Have a look at this, maybe it's your system settings are different to the date format in the cell

If your system date and time settings follow the mm/dd/yyyy format, then a formula such as =DATEVALUE(“22/6/2000”) will result in a #VALUE! error. But the same formula will display the correct value when the system's date and time is set to dd/mm/yyyy format.

 

[mse330]

Excel stores the dates as values & it starts from 1-Jan-1900 (which equals =1) and today's date (25-Feb-2024) is stored as 45347. You're getting the errors due to old dates that excel can't recognize. If you change the year in your date April 30, 1789 to April 30, 1989 it should work

 

[Peter_SSs]

From DATEVALUE help

Using the default date system in Microsoft Excel for Windows, the date_text argument must represent a date between January 1, 1900 and December 31, 9999. The DATEVALUE function returns the #VALUE! error value if the value of the date_text argument falls outside of this range.

 

[JenniferMurphy]

Excel stores the dates as values & it starts from 1-Jan-1900 (which equals =1) and today's date (25-Feb-2024) is stored as 45347. You're getting the errors due to old dates that excel can't recognize. If you change the year in your date April 30, 1789 to April 30, 1989 it should work

Aha! So is there any way I can convert those older dates to numbers?

I found this macro. It looks kinda complicated.

How to calculate ages before 1/1/1900 in Excel - Microsoft 365 Apps

Describes how to use Macro to calculate age before 1/1/1900 in Excel.

learn.microsoft.com

I think it's way past time for M$FT to add a DateValueX (for extended) that goes back at least a couple of thousand years or to whenever reliable dates were being recorded.

 

[MARK858]

@JenniferMurphy various suggestions in the thread in the link below from the other day.

Not displaying dates before 1900

im trying to get dates Before 1900 to display the dates that are meeting the criteria. but as its before 1900 it dont display, but if its 1900 onwards it has no problem displaying. all help appreicated. hope the pic helps.

www.mrexcel.com