Solved Calculate approximate birth month and year from approximate age in year and month

[nirmal]

Is there any formula to calculate approximate birth month and year in the format of 01/mm/yyyy from approximate age in year and month.
e.g. if someone gives age as 10.2 years, then what should be the approximate birthdate as on today.

 

[ebs17]

? DateSerial(Year(Date)-10, Month(Date)-2, 1)
01.11.2012

 

[bob fitz]

I'm not sure that I understand your requirement. If the age is actually 10.2 yrs rather than 10 yrs 2mths, then perhaps something like:
=DateAdd("d",-(10.2*365),Date())

 

[arnelgp]

maybe create a function:

' arnelgp
Public Function fnBirthDate(ByVal m As Variant) As Variant
Dim i As Integer, yr As Variant, mn As Variant
Dim ret
If IsNull(m) Then
    Exit Function
End If
m = m & ""
i = InStr(1, m, ".")
yr = Val(m)
If i <> 0 Then
    mn = Mid$(m, i)
    yr = Val(Replace$(m, mn, ""))
    mn = Val(Replace$(mn, ".", ""))
Else
    mn = 0
End If
ret = DateAdd("yyyy", -yr, Date)
ret = DateAdd("m", -mn, ret)
fnBirthDate = ret
End Function

?fnBirthDate(20.2)
nov-20-2002

 

[nirmal]

maybe create a function:

' arnelgp
Public Function fnBirthDate(ByVal m As Variant) As Variant
Dim i As Integer, yr As Variant, mn As Variant
Dim ret
If IsNull(m) Then
    Exit Function
End If
m = m & ""
i = InStr(1, m, ".")
yr = Val(m)
If i <> 0 Then
    mn = Mid$(m, i)
    yr = Val(Replace$(m, mn, ""))
    mn = Val(Replace$(mn, ".", ""))
Else
    mn = 0
End If
ret = DateAdd("yyyy", -yr, Date)
ret = DateAdd("m", -mn, ret)
fnBirthDate = ret
End Function

?fnBirthDate(20.2)
nov-20-2002

Sir this function is giving results correctly.
But can we make it default 1st of that month

 

[arnelgp]

Sir this function is giving results correctly

the function is somewhat "correct", since the result is based on Today's date.


modify it a bit:

' arnelgp
Public Function fnBirthDate(ByVal m As Variant) As Variant
Dim i As Integer, yr As Variant, mn As Variant
Dim ret
If IsNull(m) Then
    Exit Function
End If
m = m & ""
i = InStr(1, m, ".")
yr = Val(m)
If i <> 0 Then
    mn = Mid$(m, i)
    yr = Val(Replace$(m, mn, ""))
    mn = Val(Replace$(mn, ".", ""))
Else
    mn = 0
End If
ret = DateAdd("yyyy", -yr, Date)
ret = DateAdd("m", -mn, ret)
fnBirthDate = DateSerial(Year(ret), Month(ret), 1)
End Function

 

[nirmal]

Thank You Sir
Getting expected results