Dealing with pre 1900 dates

In the 1904 system and 1900-system, this formula always calculates the correct difference in days from 15-10-1582 according to the Gregorian calendar.
This formula also sidesteps the problem that Excel mistakenly assumes that 1900 is a leap year.

Formula:

Please Login or Register  to view this content.

@XLent a correction of 1000 years is not correct, because the minimal period all the differences are the same is 400 years. The leap rule is as follows: "If a year is divisible by 4 then it is a leap year, except for the years divisible by 100, then it is not a leap year, except for the years divisible by 400 then it is a leap year."
So you should add 400 years (4800 months) or a plural of 400 years (eg. 24000 months).

is that what you want?
(without chatGPT )

Date	Open	High	Low	Close	Subtraction
31/05/1789	0.51	0.51	0.51	0.51	30
30/06/1789	0.51	0.51	0.51	0.51	30
31/07/1789	0.5	0.5	0.5	0.5	31
31/08/1789	0.5	0.51	0.5	0.51	31
30/09/1789	0.51	0.51	0.5	0.51	31
31/10/1789	0.51	0.51	0.51	0.51	31
30/11/1789	0.51	0.51	0.5	0.5	31
31/12/1789	0.5	0.5	0.5	0.5	31
31/01/1790	0.5	0.5	0.49	0.49	28
28/02/1790	0.49	0.49	0.49	0.49	28

Are you required to use Excel for this? Other spreadsheets (LO Calc, Google Sheets) have supported dates prior to 1900 (all the way back to 1 CE Gregorian) for some time. I can open your file in LO Calc, and the formula in column G works just fine without any edits. I sometimes feel like the easiest way to work with pre-1900 dates is to use a different spreadsheet.

The only thing I would note is that your sample file includes dates back to the 18th century -- when some nations were switching over from Julian to Gregorian. As far as I know, the current systems all extrapolate Gregorian dates back in time. If you ever need to consider the switch from Julian to Gregorian, you will probably need to adjust the date serial numbers to account for it.

Not sure that helps. It would appear that, if you are restricted to Excel and only Excel, you might be better off switching to Power Query rather than Excel. However you do it, the overall consensus seems to be to abandon Excel because Excel cannot handle pre-1900 dates.

Originally Posted by MrShorty

the overall consensus seems to be to abandon Excel because Excel cannot handle pre-1900 dates.

There is no need to abandon Excel, because its possible to create a working Workaround in Excel. See post #4.

There is no need to abandon Excel, because its possible to create a working Workaround in Excel.

You are, of course, correct and I stand corrected. It still seems like a problem to me if we still need to twist ourselves into knots in Excel to create a workaround for something that other spreadsheets have long made a simple solution for.

Correction

Please Login or Register  to view this content.

The CSV file has stripped out the query - can you attach the XLSX file?

EDIT: Thanks!