User-Defined Functions

css navigation by Css3Menu.com

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


© 1999-2024

Updated:  01/23/2024 13:34
This page added:  04 April 1999