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.

 

[RoryA]

I am criticizing it for not being consistent. Dates in Excel are in units of days, so this code should do the same.

With respect, I don't think that makes any sense at all. Are you honestly suggesting that any date function, regardless of purpose, should return a number of days? If I see a function that states that it returns someone's age in years, I don't expect a result in days, nor would I want one. The fact that a function doesn't fit your purpose does not mean it is not fit for its stated purpose.

 

[JenniferMurphy]

Here is another way to write your function (a little more compact)...

Public Function IsLeapYear(pYear As Long) As Boolean
  IsLeapYear = Month(DateSerial(pYear, 2, 29)) = 2
End Function

Very nice. I like it. Thanks.

But, of course, I just had to put my mark on it. I added tests for year values that I consider invalid that are not rejected by DateSerial, which does some interesting things with numbers that are out of range.

DateSerial function (Visual Basic for Applications)

Public Function IsLeapYear(pYear As Variant) As Boolean
' Rule out value errors. DateSerial rejects years > 9999, but converts all others.
If IsEmpty(pYear) Or Not IsInteger(pYear) Or pYear < 100 Then
  IsLeapYear = CVErr(xlErrValue)
  Exit Function
End If
IsLeapYear = Month(DateSerial(pYear, 2, 29)) = 2
End Function

The image of the test results is too large to upload here, so I put it in this Dropbox folder:

20200819 IsLeapYear test results

Shared with Dropbox

www.dropbox.com

I am now satisfied that this UDF correctly identifies all leap years from year 100 to year 9999.

 

[shg]

A few other things to consider (Your story: Why did 11 days disappear in 1752?):

• Dec. 31, 1750, was followed by Jan. 1, 1750 (under the “Old Style” calendar, December was the 10th month and January the 11th)
• March 24, 1750, was followed by March 25, 1751 (March 25 was the first day of the “Old Style” year)
• Dec. 31, 1751, was followed by Jan. 1, 1752 (the switch from March 25 to Jan. 1 as the first day of the year)
• Sept. 2, 1752, was followed by Sept. 14, 1752 (drop of 11 days to conform to the Gregorian calendar)

I expect Microsoft's cutoff date was deliberate to avoid endless confusion and carping.

 

[Rick Rothstein]

@Jennifer,

I note you are using a function called IsInteger in the code you posted... is that your own function? If so, could you post it for us to see?

As for the missing 11 days that shg noted, there was also a dramatic staggering of acceptance (in some cases resulting in more or less gap days) of the Gregorian Calendar among the countries of the world. See Wikipedia's chart showing the years of acceptance among coutries here...

List of adoption dates of the Gregorian calendar per country - Wikipedia

So you kind of need to becareful when working with early dates.

 

[mikerickson]

Excel VBA accepts negative dates quite happily

Function DaysBetween(aDateString As String, bDateString As String) As Long
    Dim aDate As Date, bDate As Date
    aDate = DateValue(aDateString)
    bDate = DateValue(bDateString)
    DaysBetween = CLng(bDate - aDate)
End Function

 

[JenniferMurphy]

A few other things to consider (Your story: Why did 11 days disappear in 1752?):

• Dec. 31, 1750, was followed by Jan. 1, 1750 (under the “Old Style” calendar, December was the 10th month and January the 11th)
• March 24, 1750, was followed by March 25, 1751 (March 25 was the first day of the “Old Style” year)
• Dec. 31, 1751, was followed by Jan. 1, 1752 (the switch from March 25 to Jan. 1 as the first day of the year)
• Sept. 2, 1752, was followed by Sept. 14, 1752 (drop of 11 days to conform to the Gregorian calendar)

I expect Microsoft's cutoff date was deliberate to avoid endless confusion and carping.

(sigh) Thanks for that info. I knew there was some funny business with dates going back a few centuries what with changing calendars and all. But I didn't have the time to sort it all out. I was writing a paper on American history, so I only needed to go back to 1776 or so.

Maybe I'll just change my functions to reject dates prior to 1751.

I consider it grossly unprofessional and inept on the part of M$FT not to provide comprehensive date functions that operate correctly back at least several centuries, if not all the way back to 0 AD.

 

[JenniferMurphy]

@Jennifer,

I note you are using a function called IsInteger in the code you posted... is that your own function? If so, could you post it for us to see?

Ah, yes. I forgot about that. There are so many IsThis and IsThat functions and they differ between Excel and VB that I can't keep track. Here's my code:

Function IsInteger(ByVal pValue) As Boolean
  If Not IsNumeric(pValue) Or IsEmpty(pValue) Then IsInteger = False: Exit Function
  IsInteger = pValue = Int(pValue)
End Function

So you kind of need to be careful when working with early dates.

So I see...

 

[JenniferMurphy]

Using Formula to Calculate Age Before 1900

Cell birth date A1 cell today's date B1

=DATEDIF(REPLACE(TEXT(A1,"dd/mm/yyyy"),7,4,RIGHT(TEXT(A1,"dd/mm/yyyy"),4)+1000),REPLACE(TEXT(B1,"dd/mm/yyyy"),7,4,RIGHT(TEXT(B1,"dd/mm/yyyy"),4)+1000),"y")

Decio

It looks like you are converting dates prior to 1900 to dates after 1900 by adding 1000 to the year. So instead of passing the year 1200 to DateDif, you pass it 2200. Correct?

This not only has all the problems with lost days already pointed out by others, but since 1000 is not an even multiple of 400, it will get the leap years wrong. No? While 1200 would be a leap year according to the multiple of 400 rule, 2200 would not be.

And, I'd need to add a test for 1900 so I don't add 1000 to years after that date.

 

[Rick Rothstein]

Here's my code:

Function IsInteger(ByVal pValue) As Boolean
  If Not IsNumeric(pValue) Or IsEmpty(pValue) Then IsInteger = False: Exit Function
  IsInteger = pValue = Int(pValue)
End Function

Your function only tests if a number is a real number, text numbers are excluded... is that correct? In other words, IsInteger("123") or IsInteger(A1) where A1 is formatted as text is intended to return False, correct?

 

[JenniferMurphy]

Your function only tests if a number is a real number, text numbers are excluded... is that correct? In other words, IsInteger("123") or IsInteger(A1) where A1 is formatted as text is intended to return False, correct?

Yes, I guess that's true. I don't usually deal in text "numbers", so it didn't occur to me to include them in the code.

I've made a note of that in the code in case I need it in the future. Thanks