Google Sheets Days Function with Pre-1900 years

It appears to me, too, that it is about the year 100. I don't have a solution, but I went through the following testing steps:

1) =ISTEXT(C1) [copied down and across] yields TRUE for almost all entries, so Google is seeing these as "dates stored as text". Part of understanding the problem will be to understand how GoogleSheets converts dates stored as text to date serial numbers.
2) =VALUE(C1) [copied down and across] and formatted as number gives me a look at the serial numbers. For the "two digit" years (00xx years), all of the serial numbers are numbers between 10000 and 40000. For the three digit years (0xxx), the numbers are -660000 to -600000. Formatting these numbers as date with 4 digit year (mm/dd/yyyy) shows that the two digit year entries have all been interpreted as 20th century dates. The 3 digit year entries have been interpreted as 2nd and 3rd century dates. As a quick test (to see if Google is adhering to the standard "29" cutoff for 2 digit years, I change one of the entries to mm/dd/0011, and I get a 2011 date, so Google will even return a 21st century date in this scenario).

With that testing, the problem seems to be that Google is interpreting two digit years as 20th/21st century dates, and it isn't smart enough to recognize that 00xx is a 4 digit year (but let's not complain too much because Excel cannot recognize any date before 1900).

I don't have a specific solution, but I would suggest figuring out how many days to subtract from the 2 digit year entries so that they will return the correct serial number for the 1st century date. That probably taking a little time to figure out what date corresponds to serial number 0 and what serial number corresponds to 1 Jan 0001 AD. Part of that might involve exploring if Google is simply extrapolating the modern Gregorian calendar backwards in time or if it is correctly tracking backwards through Gregorian to Julian to whatever calendar system best represents 1st century dates and so on. If you need help with that, let us know, but it looks like the solution will involve figuring out how to get Google to correctly convert the 2 digit years to 1st century date serial numbers.

By putting 1/1/0100 and 1/1/2000 into separate cells and subtracting them, it looks like Google thinks there are 693,960 days between those two dates 1900 years apart. So it looks like, if you subtract 693960 from those dates that return a 20th or 21st century date, they should return the correct 1st or 2nd century date. I would expect the formula to look something like =IF(date>=DATEVALUE("1/1/1900"),date-693960,date) or =IF(DATEVALUE(textdate)>=DATEVALUE("1/1/1900"),DATEVALUE(textdate)-693690,DATEVALUE(textdate)) or similar

I don't have any expertise in the development of ancient calendars to know if that value is exactly right. It just appears to give the expected result in your spreadsheet.

I hovered over the little red error indicator in A22, and Google informed me that the "DATEVALUE parameter '0.003527336861' cannot be parsed to date/time." I notice that you are using DATEVALUE(10/27/105) in the formula. 10/27/105 will be interpreted as a division statement (10 divided by 27 divided by 105 which evaluates to 0.003...). DATEVALUE() requires a text string argument, so I think it will work just fine if you will enclose the date text in quotation marks DATEVALUE("10/27/105").

I am assuming you are talking about the current values in A22 and A23? The results look correct for the formulas that are entered:

In A22, the formula converts "10/27/0105" to a serial number (-655309) and compares to the serial number for 1/1/1900. The first is less than the second, so it goes to the "value_if_false" argument and returns the serial number for "10/27/0105" (-655309). As near as I can tell, this is the correct serial number for that 2nd century date, so this result looks correct to me.

In A23, the formula seems to compare the text string "10/27/0105" (not converted to a date serial number) to the serial number for 1/1/1900. Since text is always larger than numbers, this test returns TRUE, and it goes on to the "value_if_true" argument. In this calculation, google seems to recognize that it cannot subtract a number from text, so it attempts to convert the date text string to a number by converting to the appropriate serial number (-655309) then subtracting 693690 from that -- which correctly yields -1349269. Again, this looks correct for the formula entered into the cell, but this is not the correct date serial number for 10/27/0105.

The formula in A22 looks correct to me -- for returning the correct serial number for a 1st through 3rd century date. Note that this is not the formula to get the difference (or the number of days) between two dates. To get the number of days between two dates, you can use the DAYS() function like you are using in column E -- something like =DAYS(IF(DATEVALUE(datetext1)>...),IF(DATEVALUE(datetext2)>...)) where we are nesting formulas like the current one in A22 inside of the DAYS() function. The two IF() formulas make sure that the correct serial number is returned for each date, then the DAYS() function can perform the subtraction.

Does any of that help, or did I misunderstand when you said these aren't the numbers you are looking for?

One caution -- don't replicate my mistakes. In post #8, I wrote that you subtract 693690 from a 20th century date to get the equivalent 1st century date, but that is wrong. Note that in post #4, I gave the correct value that is 693960. For future reference, I get this value by from a formula like =DAYS(DATEVALUE("1/1/0100"),DATEVALUE("1/1/2000") that computes how many days Google thinks there are between "today" and the 2nd century. You should make that correction to your formula.

I note that, with that correction, the formula returns 2164 and not 2165. I expect that this is due to which years Google is considering to be leap years, which is probably problematic because, as I observed earlier, I don't know if Google is merely extrapolating the Gregorian calendar back in time to long before the Gregorian calendar existed or if it is backing through the different calendars as they historically existed. It appears to me that Google is extrapolating the current Gregorian calendar back in time, which means that it treats even 100 years (0100, 1900, 2000, 2100) as non-leap years (yielding 2164 days for this calculation) where you want to treat 100 as a leap year (to give 2165). You'll probably need to spend some time with Google's choice of days to understand exactly which years Google is choosing to be leap years and which not and then add some conditions to the formula that will account for the years that Google does not consider leap years but that you want to treat as leap years.