MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dates


Posted by Alan Lucking on December 20, 2001 6:02 AM

How can I get Excel to return a persons age in Years Months and Days from their Date of Birth.


Posted by Pierre on December 20, 2001 6:50 AM

Hi,

You have first to be sure that your date is actually a date, you use isdate function then you continue as below:

Sub myDate()
Dim myDate As Date
Dim AgeInYear As Integer
Dim AgeinMonth As Integer
Dim AgeinDay As Integer

myDate = Cells(1, 1)
If IsDate(myDate) Then
AgeInYear = DateDiff("yyyy", myDate, Date)
AgeinMonth = DateDiff("m", myDate, DateSerial_
(Year(myDate), Month(Date), Day(Date)))
AgeinDay = DateDiff("d", myDate, DateSerial_
(Year(myDate), Month(myDate), Day(Date)))
If AgeinDay < 0 Then
AgeinMonth = AgeinMonth - 1
AgeinDay = AgeinDay + DateDiff("d", Date,_
DateAdd("m", 1, Date))
End If
If AgeinMonth < 0 Then
AgeInYear = AgeInYear - 1
AgeinMonth = AgeinMonth + 12
End If
MsgBox "I am " & AgeInYear & " years, " & _
AgeinMonth & " month and " & AgeinDay & " days _
old"
End If
End Sub


end sub

Posted by Pierre on December 20, 2001 6:58 AM

Sorry little mistake,
the following code is the correct one:
Sub myDate()
Dim myDate As Date
Dim AgeInYear As Integer
Dim AgeinMonth As Integer
Dim AgeinDay As Integer

myDate = Cells(1, 1)
If IsDate(myDate) Then
AgeInYear = DateDiff("yyyy", myDate, Date)
AgeinMonth = DateDiff("m", myDate, DateSerial(Year(myDate), Month(Date), Day(Date)))
AgeinDay = DateDiff("d", myDate, DateSerial(Year(myDate), Month(myDate), Day(Date)))
If AgeinDay < 0 Then
AgeinMonth = AgeinMonth - 1
AgeinDay = AgeinDay + DateDiff("d", myDate, DateAdd("m", 1, myDate))
End If
If AgeinMonth < 0 Then
AgeInYear = AgeInYear - 1
AgeinMonth = AgeinMonth + 12
End If
MsgBox "I am " & AgeInYear & " years, " & AgeinMonth & " month and " & AgeinDay & " days old"
End If

End Sub

end sub

Posted by Alan Lucking on December 21, 2001 3:44 AM

Thanks Pierre. Unfortunately I cannot get this to work and get the error messsage "Sub or Function not defined" when it gets to Datediff. Any chance of e-mailing me a spreadsheet with it in?
Alan.Lucking@Talk21.com