Several of my genealogy friends had asked for a simple formula to determine the number of days between 2 dates. I was able to do years and days but months escaped me. Finally someone came through.

The next challenge is that one of these friends says that a tombstone says, “Died 10 October 1922 aged 50 years, 100 days”. What was the date of birth? Did the counter account for leap years? Excel cannot handle a date before 1900.

I made one minor change because I always use ‘Option Explicit’ and must DIM all variables, a good habit to get in. Thanks to Brian W.

Option Explicit Function YMD(Day1 As Date, Day2 As Date) As String Dim years, months, days, m years = Year(Day2) - Year(Day1) If Month(Day1) > Month(Day2) Then years = years - 1 End If If Month(Day2) < Month(Day1) Then months = 12 - Month(Day1) + Month(Day2) Else months = Month(Day2) - Month(Day1) End If If Day(Day2) < Day(Day1) Then months = months - 1 If Month(Day2) = Month(Day1) Then years = years - 1 months = 11 End If End If days = Day(Day2) - Day(Day1) If days < 0 Then m = CInt(Month(Day2)) - 1 If m = 0 Then m = 12 Select Case m Case 1, 3, 5, 7, 8, 10, 12 days = 31 + days Case 4, 6, 9, 11 days = 30 + days Case 2 If (Year(Day2) Mod 4 = 0 And Year(Day2) _ Mod 100 <> 0) Or Year(Day2) Mod 400 = 0 Then days = 29 + days Else days = 28 + days End If End Select End If YMD = CStr(years) + " years " + CStr(months) _ + " months " + CStr(days) + " days " End Function |