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]

That page does specifically state that is a function to return age in years, so I don't really think you can criticise it for doing exactly that.

 

[Jon von der Heyden]

M$FT provides a "macro" (AgeFunc) that it says solves the problem.

No it doesn't:

function to calculate the age (in years)

I don't know what idiot wrote this, but it's **** near worthless. {blah} {blah} Idiots.

I suspect the person who wrote this did so with only the best intentions, and probably for free. I suspect it has also addressed the need to calculate years for countless people seeking to do exactly this. It's a shame that the person who went to these lengths gets branded an idiot.

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

It would appear that you already know how to solve this problem.

 

[Rick Rothstein]

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

Given that you are in the US locale, this UDF should work...

Function DaysBetween(EarlyDate As Variant, LateDate As Variant) As Long
  DaysBetween = DateDiff("d", EarlyDate, LateDate)
End Function

Note: If you are not in the US locale, I have no idea whether it will work for you or not because I am in the US locale and cannot test it for other locales.

 

[JenniferMurphy]

Given that you are in the US locale, this UDF should work...

Function DaysBetween(EarlyDate As Variant, LateDate As Variant) As Long
  DaysBetween = DateDiff("d", EarlyDate, LateDate)
End Function

Wow!!! Thank you. And it even supports negative differences.

Too bad the Excel developers can't figure out how to support this in Excel itself.

Note: If you are not in the US locale, I have no idea whether it will work for you or not because I am in the US locale and cannot test it for other locales.

I'm in California. I think we're still in the US. {moderator note: political remark removed}.

 

[deciog]

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

 

[JenniferMurphy]

After doing a little research on this, I feel that my criticism of M$FT is, if anything, understated.

Rick's DaysBetween code, based on the VB function DateDiff, works beautifully for all dates from 1/1/0000 to 12/31/9999.

I renamed it MyDateDiff in case I want to add another parameter so it can support all of the DateDiff units. I also changed the names of the parameters because they can be entered in reverse order and will get negative numbers.

Function MyDateDiff(Date1 As Variant, Date2 As Variant) As Long
  MyDateDiff = DateDiff("d", Date1, Date2)
End Function

Since the VB developers are smart enough to handle dates before 1900, the Excel developers must be too stupid or too lazy to use code M$FT already has working. The limit of 1900 seems arbitrary and silly. Even if it's for some legacy reason, the VB solution should be available without having to resort to code.

Then I thought I detected an error in the leap year calculations. So I wrote another little UDF, IsLeapYear, to determine if a year is or is not a leap year. A year is a leap year if it is a multiple of 4, but not a multiple of 100, unless it's also a multiple of 400. So 2004 is, 2100 is not, but 2000 is. Here's that code:

Public Function IsLeapYear(pYear As Double) As Boolean
IsLeapYear = False
If IsInteger(pYear) And (pYear >= 0 Then IsLeapYear = IsDate("2/29/" & pYear)
End Function

I then created a sheet with a list of years between 0000 and 9999 (Col C). For each year, I generated two dates: 2/28 (Col E) and 3/01 (Col F) and asked my IsLeapYear function whether the year was a leap year (Col D). I then had both Excel (Col H) and VB (Col G) calculate the number of days from 2/28 to 3/01 in each year. If IsLeapYear said is was a leap year, the difference should be 2 days. Otherwise, just 1.

This example shows that both Excel and VB get it right for leap years, but Excel quits below 1900.

This example deals with years that are not leap years. VB gets them all right. Excel not only quits before 1900, but incorrectly believes that 2/29/1900 is a valid date, which it is not.

So, not only is the Excel date code incomplete, but buggy.

 

[JenniferMurphy]

That page does specifically state that is a function to return age in years, so I don't really think you can criticise it for doing exactly that.

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

 

[JenniferMurphy]

I suspect the person who wrote this did so with only the best intentions, and probably for free. I suspect it has also addressed the need to calculate years for countless people seeking to do exactly this. It's a shame that the person who went to these lengths gets branded an idiot.

It's on a M$FT webpage, so they are responsible for the quality. The code is terrible.

It would appear that you already know how to solve this problem.

How so? Not that this has anything to do with anything.

If you want to be an apologist for M$FT, go ahead. I'm with Woody Leonhard who has documented hundreds, if not thousands, of bugs in Word -- many of which have been known for decades.

 

[Rick Rothstein]

So I wrote another little UDF, IsLeapYear, to determine if a year is or is not a leap year. A year is a leap year if it is a multiple of 4, but not a multiple of 100, unless it's also a multiple of 400. So 2004 is, 2100 is not, but 2000 is. Here's that code:

Public Function IsLeapYear(pYear As Double) As Boolean
IsLeapYear = False
If IsInteger(pYear) And (pYear >= 0 Then IsLeapYear = IsDate("2/29/" & pYear)
End Function

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