Determine Years, Months, & Days between 2 Dates
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 and a top ’o the hat 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
|