Finding the Difference of Two Dates from Text Boxes

Question

I would like to calculate age by getting the difference between the contents of two text boxes using VBA.

I have tried a lot of code that didn't work, and in the end I used the below module. The user form will show the result from the Excel sheet.

I want to know how to calculate the age using the user form text box only.

Module:

 Function Age(Date1 As Date, Date2 As Date) As String
    Dim d As Long, m As Long, y As Long
    If Date2 < Date1 Then
        Age = "-error-"
        Exit Function
    End If
    m = DateDiff("m", Date1, Date2)
    d = DateDiff("d", DateAdd("m", m, Date1), Date2)
    If d < 0 Then
        m = m - 1
        d = DateDiff("d", DateAdd("m", m, Date1), Date2)
    End If
    y = m \ 12
    m = m Mod 12
    If y > 0 Then
        Age = y & " Year"
        If y > 1 Then
            Age = Age & "s"
        End If
    End If
    If Age <> "" And m > 0 Then
        Age = Age & ", "
    End If
    If m > 0 Then
        Age = Age & m & " Month"
        If m > 1 Then
            Age = Age & "s"
        End If
    End If
    If Age <> "" And d > 0 Then
        Age = Age & ", "
    End If

VBA used in the user form is:

Private Sub txtDoB_AfterUpdate()
    row_number = 0
    Do
    DoEvents

    row_number = row_number + 1
    item_in_review = Sheets("Data").Range("A" & row_number)

    If item_in_review = txtSN.Text Then
        Sheets("Data").Range("B" & row_number) = txtDoB.Text
        txtAge.Value = Sheets("Data").Range("D" & row_number)     
    End If

    Loop Until item_in_review = ""    
End Sub

could you give some examples of `Date1` and `Date2`? and tell us what do you expect as a result for that sample dates. — Kazimierz Jawor, Nov 23 '13 at 11:18
ex., Date 1 is 20 April 2009 and Date 2 is 15 Sep 2013 So the result should be 4 years, 4 Months , 26 Days — user3024687, Nov 23 '13 at 11:31
do you have them written in this way, as text `20 April 2009` or as date `2009-04-20`. What is expected result of `Age function` for these dates? — Kazimierz Jawor, Nov 23 '13 at 11:33
Why use a textbox for calculating dates? You may want to use [THIS](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba/12013961#12013961)? — Siddharth Rout, Nov 23 '13 at 16:16

score 0 · Answer 1 · answered Apr 11 '15 at 14:26

The problem of converting "20 April 2009" to a Date type is the month name that your MonthName(m) function results are different.

Well you can handle it manually:

Function toDate(strDate As String) As Date
    Dim i As Integer
    For i = 1 To 12
        strDate = Replace(strDate, myMonthName(i), CStr(i))
    Next i
    toDate = CDate(strDate)
End Function

' Add other months as your input is
Function myMonthName(m As Integer)
    Select Case m
        Case 1: myMonthName = "January"
        ..
        Case 4: myMonthName = "April"
        ..
    End Select
End Function

And also I can suggest you this function to calculate differential of to Dates:

Function diffDate(date1 As Date, date2 As Date) As String
    Dim dTemp As Date
    dTemp = DateSerial(Year(date1), Month(date1), Day(date1)) - DateSerial(Year(date2), Month(date2), Day(date2))
    diffDate = Trim(Year(dTemp) - 1900) & " Year(s), " & Trim(Month(dTemp)) & " Month(s) " & Trim(Day(dTemp)) & " Day(s)"
End Function

Finding the Difference of Two Dates from Text Boxes

1 Answers1