Google sheets calculations for dates before 1900 - A possible solution

Question

I am trying to do calculations without scripts. A solution can be to sum a far in the future date (31/12/9999) +1 and use that value to do calculations

Cell A1 = 1/2/1872 -> -10.194,00
Cell A2 = 31/12/9999 -> 2.958.465,00
Cell A3 = A1-A2+1 -> 2.948.270,00 -> 01/02/9972

I can adjust the year to a more confortable, year that is distant from edges (99999 and 1900) -> I use as offset 4000 which should preserve original leap year. A4: =DATE(YEAR(A3)-8100+4000;MONTH(A3);DAY(A3)) -> 01/02/5872

at this point I can use A4 to do most of calculations on dates and calculate back any adjusted date by using DATEVALUE()

This does of course take into consideration past dates that have issues with current official calentars; it seems that 19th century is ok. I haven't tested/ported it to Excel.

Does anybody confirm it works?

Yuma

It seems you're trying to answer your own question. See Self-answer – Ligan

Welcome to Stack Overflow. The "maximum date" in Google Sheets is 90000 years later, on 99999-12-31. See this answer for an explanation of how date and time values work in spreadsheets. – Pectoralis

Answer 1

the logic of your formula may be correct, but more factors must be considered when playing with the calendar as humanity likes to adjust even the rules of adjustment. here are a few examples:

• the longest year in history: 46 BCE (708 AUC) lasting 445 days known as "the last year of confusion" as Ceasar added 3 more months (90 days) so that the next year (45 BCE) would start right after the winter solstice.

• the shortest year in history: 1582 CE lasting 355 days where October had only 21 days (4th Oct. was followed by 15th Oct.) but also it depends where you are because British Empire decided to reinvent the wheel by accepting the "1582-wheel" in the year 1752 CE where September had only 19 days (2nd Sep. was followed by 14th Sep.) resulting the year to have 355 days as well. however, if we are technical, the British Empire also had a year that lasted only 282 days because their "old" new year started on 25 March and not 1 January therefore the year 1751 CE started on 25th Mar and ended on 31st Dec. Turkey, for example, joined the "gregorian train" in 1st Jan 1927 CE after their December of 1926 CE had only 18 days so that year was long only 352 days. the latest country to adopt the gregorian calendar was Saudi Arabia in 2016 CE when they jumped from 1437 AH

• the year zero: does not exist. 31st Dec. 1 BCE was followed by 1st Jan. 1 CE

  753 AUC = 1 BCE
  754 AUC = 1 CE
  

  also, dude who invented this nonsense was born around 1223 AUC (470 CE) so that speaks for itself. this is important because offsetting DATEVALUE needs to be done in such a way that the calculation will not cross 0 eg. not drop bellow -693593:

  =TO_DATE(-694324)   -   incorrect datevalue   -   01/01/00-1
  
  =TO_DATE(-693678)   -   incorrect datevalue   -   08/10/0000
  
  =TO_DATE(-693593)   -   1st valid datevalue   -   01/01/0001
  
  =TO_DATE(35830290)  -   last valid datevalue  -   31/12/99999
  

• it's also worth mentioning that 25th Dec. 200 CE was not Friday on the Roman peninsula because people in that era used 8-day system

• there are many calendar systems each with its own set of rules and up to this date there are still countries that do not recognize the gregorian calendar as a standard so if you want to re-live the year 2021 go to Ethiopia where today's 9 Oct. 2022 CE = 29 Mes. 2015 EC on the other hand if you prefer to live in the future try Nepal where today's 9 Oct. 2022 = 23 Ash. 2079 BS

Blase

thank you! I am dealing with 19th century dates only so no problem. – Yuma