# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] Google Sheets Days Function with Pre-1900 years

## profess

Hello, I am trying to use the function '=DAYS()' with dates before the year 1900. I have a sample on a google sheet. All rows work with the early dates except Row 5. For some reason it gives me a large negative number instead of the days in between. I suspect it might have something to do with the year 100 but I am not sure. Any help or insight is much appreciated.

Link to Sample Google Sheet:

https://docs.google.com/spreadsheets...K0Q/edit#gid=0

----------


## MrShorty

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.

----------


## profess

Thank you for replying so quickly. I would greatly appreciate the help in identifying the Gregorian vs. Julian calendar and how to set the Google sheet to that.

Fortunately, it is only affect one row of data so if need be I can calculate by hand.

----------


## MrShorty

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.

----------


## profess

Hi, can you please check the formula? I entered it into my sample with I am still getting #VALUE!

Thank you for all your help!

----------


## MrShorty

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

----------


## profess

Thank you for the advice, but I am still getting negative numbers. At least there is no error but it is not the number I am looking for.

----------


## MrShorty

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?

----------


## profess

Thank you! That is what I meant, but to get the days in between the two dates I need the serial numbers of each, correct? However, I'm using 693690 for both dates and I'm getting 1894 days, not 2165 that I need. Do I need to change the number for the second date? If so, do you know how I would find that out? I tried the function that you gave me as well as the DATEVALUE function. If you can point me in the right direction I think we can finally resolve this thread! Thank you again for all your help

----------


## MrShorty

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.

----------


## profess

Thank you, that worked! I think I am finally beginning to understand what you have been trying to explain this whole time.

----------

