How to calculate the Years, Months and Days in MS Access Query

Question

Dear Community,

Is there experts who can guide me on the formula in MS Access Query to calculate the Years, Months and Days between two dates? Thank you.

Answer 1

Try the DateDiff function.

Answer 2

You can use my function FormatAgeYearsMonthsDays. 

Public Const MonthsPerYear          As Integer = 12

' Formats the output from AgeMonthsDays.

'

' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.

'

Public Function FormatAgeYearsMonthsDays( _

    ByVal DateOfBirth As Date, _

    Optional ByVal AnotherDate As Variant) _

    As String

    Dim Years       As Integer

    Dim Months      As Integer

    Dim Days        As Integer

    Dim YearsLabel  As String

    Dim MonthsLabel As String

    Dim DaysLabel   As String

    Dim Result      As String

    Months = AgeMonthsDays(DateOfBirth, AnotherDate, Days)

    Years = Months \ MonthsPerYear

    Months = Months Mod MonthsPerYear

    YearsLabel = "year" & IIf(Years = 1, "", "s")

    MonthsLabel = "month" & IIf(Months = 1, "", "s")

    DaysLabel = "day" & IIf(Days = 1, "", "s")

    ' Assemble output.

    Result = CStr(Years) & " " & YearsLabel & ", " & CStr(Months) & " " & MonthsLabel & ", " & CStr(Days) & " " & DaysLabel

    FormatAgeYearsMonthsDays = Result

End Function

' Returns the difference in full months from DateOfBirth to current date,

' optionally to another date.

' Returns by reference the difference in days.

' Returns zero if AnotherDate is earlier than DateOfBirth.

'

' Calculates correctly for:

'   leap Months

'   dates of 29. February

'   date/time values with embedded time values

'   any date/time value of data type Date

'

' DateAdd() is, when adding a count of months to dates of 31th (29th),

' used for check for month end as it correctly returns the 30th (28th)

' when the resulting month has 30 or less days.

'

' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.

'

Public Function AgeMonthsDays( _

    ByVal DateOfBirth As Date, _

    Optional ByVal AnotherDate As Variant, _

    Optional ByRef Days As Integer) _

    As Long

    Dim ThisDate    As Date

    Dim Months      As Long

    If IsDate(AnotherDate) Then

        ThisDate = CDate(AnotherDate)

    Else

        ThisDate = Date

    End If

    ' Find difference in calendar Months.

    Months = DateDiff("m", DateOfBirth, ThisDate)

    If Months < 0 Then

        Months = 0

    Else

        If Months > 0 Then

            ' Decrease by 1 if current date is earlier than birthday of current year

            ' using DateDiff to ignore a time portion of DateOfBirth.

            If DateDiff("d", ThisDate, DateAdd("m", Months, DateOfBirth)) > 0 Then

                Months = Months - 1

            End If

        End If

        ' Find difference in days.

        Days = DateDiff("d", DateAdd("m", Months, DateOfBirth), ThisDate)

    End If

    AgeMonthsDays = Months

End Function

You can copy-paste into a new module.

Answer 3

Hi GustavBrock,

Thank you. But is it possible to have a formula in Query instead of VBA?

Answer 4

Thank you xps350https://answers.microsoft.com/en-us/profile/aa8d8dc6-c885-490b-bbcf-589c29bd2f57

Thank you xps350.

Answer 5

You use the function in a query:

Select

    DateFrom, DateTo, FormatAgeYearsMonthsDays([DateFrom], [DateTo]) As Age

From

    YourTable