Dates before 1/1/1900

[JenniferMurphy]

Is there a UDF that will calculate the number of days between two dates even if one or both are before 1/1/1900?

On this page,

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.

docs.microsoft.com

M$FT provides a "macro" (AgeFunc) that it says solves the problem. I don't know what idiot wrote this, but it's **** near worthless. The biggest problem is that it returns the number of years, not days, so it's not compatible with everything M$FT does with dates. Then, instead of returning a floating point number, it rounds down. So two dates that are 364 days apart are reported as 0 years. Idiots.

Why the h*ll doesn't M$FT finally support dates before 1/1/1900 by simply using negative numbers.

 

[JenniferMurphy]

I think I'm done with dates before 1900. My MyDateDiff UDF seems to be working, albeit ignoring the date/calendar anomalies prior to 1750 as pointed out above.

I created a sheet to test it. The image was too large to upload here, so I uploaded it to the same Dropbox folder:

20200819 IsLeapYear test results

Shared with Dropbox

www.dropbox.com

It's the one with MyDateDiff in the filename. The last column (N) compares Date1 (Col E) with today() showing (a) that the differences work all the way from 400 to 9999 and (b) that the dfates can be in either order.

It also shows the bug in the Excel date algorithm that makes 1900 a leap year. I'd like the M$FT apologists to explain that one away.

 

[RoryA]

I'd like the M$FT apologists to explain that one away.

Rule 1: "

Members should post in a way that is respectful towards other users, and consider carefully before making posts if any of their content might cause offence"​

 

[Jon von der Heyden]

It also shows the bug in the Excel date algorithm that makes 1900 a leap year. I'd like the M$FT apologists to explain that one away.

It is apparently by-design as the same quirk existed in Lotus 123, and MS chose to mimic to enable compatibility. In fact, it is for this very reason that Excel also has the 1904 date system (as far as I know).

Respectfully,
Jon

 

[Rick Rothstein]

To follow up on Jon's comment, here is Microsoft's explanation for maintaining the Leap Year bug...

Excel incorrectly assumes that the year 1900 is a leap year - Office

 

[JenniferMurphy]

It is apparently by-design as the same quirk existed in Lotus 123, and MS chose to mimic to enable compatibility. In fact, it is for this very reason that Excel also has the 1904 date system (as far as I know).

IMHO, maintaining compatibility with a "quirk" (error, bug) is wrong-headed and lazy. The responsible way would be to provide two pieces of code -- one that does it correctly and one that is compatible with the previous code. Then let the user choose which they want to use via a parameter or a setting. I'd make the correct way the default.

 

[JenniferMurphy]

To follow up on Jon's comment, here is Microsoft's explanation for maintaining the Leap Year bug...

Excel incorrectly assumes that the year 1900 is a leap year - Office

Thanks. I stand by my comments above. And this "explanation" does not explain why there is no correct solution for dates before 1900.

 

[Rick Rothstein]

One thing not emphasized in the article I linked to is that fact that, at the time, Lotus as the industry leader in spreadsheets and Microsoft was trying to break into the market with its new products. Given that, as their products gained a foothold in the market, it was important to maintain exact compatibility with Lotus as files would be transferred between Lotus users and Multiplan or Excel users and the files had to work exactly the same in both worlds. If they corrected the bug or provided date handling before 1900 (which Lotus did not do) then Lotus users would be wary of moving from a platform where all their files worked to one that, while maybe better, would prevent them from sharing modifications to workbooks with other members of their organization or their clients who were still using Lotus. Microsoft's decision back then was... make a better product and risk having only a miniscule presence in the spreadsheet market place or make their product fully cross compatible with the Lotus world and give themselves the chance to build up world dominance in the spreadsheet market which is exactly what happened. From Microsoft's viewpoint, I am sure that even today they think they made the right decision back then.

 

[MARK858]

Just to say that it is not just Microsoft who do the 1900 cutoff, it is exactly the same with Google Sheets.

 

[JenniferMurphy]

One thing not emphasized in the article I linked to is that fact that, at the time, Lotus as the industry leader in spreadsheets and Microsoft was trying to break into the market with its new products. Given that, as their products gained a foothold in the market, it was important to maintain exact compatibility with Lotus as files would be transferred between Lotus users and Multiplan or Excel users and the files had to work exactly the same in both worlds. If they corrected the bug or provided date handling before 1900 (which Lotus did not do) then Lotus users would be wary of moving from a platform where all their files worked to one that, while maybe better, would prevent them from sharing modifications to workbooks with other members of their organization or their clients who were still using Lotus. Microsoft's decision back then was... make a better product and risk having only a miniscule presence in the spreadsheet market place or make their product fully cross compatible with the Lotus world and give themselves the chance to build up world dominance in the spreadsheet market which is exactly what happened. From Microsoft's viewpoint, I am sure that even today they think they made the right decision back then.

Yes, I understand. I was working as a software engineer at the time. I have no problem with M$FT providing compatibility to a flawed product. My criticism is that they did not provide a path out of the flaw or a way for those who did not need the compatibility to have flawless code -- and still haven't.

 

[JenniferMurphy]

Just to say that it is not just Microsoft who do the 1900 cutoff, it is exactly the same with Google Sheets.

That's not exactly a ringing endorsement. Google Sheets is a muddled mess -- essentially unusable for anything non-trivial. Excel killed Lotus. It has nothing whatsoever to fear from Google Sheets.

Don't get me started on Google. They are an order of magnitude worse than M$FT. "Don't Be Evil"? Ha!