Calcualating age by month day and year

[CandiCorn5000]

I am trying to calculate the age of employees on the date of the injury...not
just by the year. I have been trying this formula...

=DateDiff("yyyy",[DOB],[Date of Injury])+(Format([DOB],"mmdd")>Format[Date
of Injury],"mmdd"))

any suggestions?

 

[raskew via AccessMonster.com]

Hi -

Try copying/pasting this to a standard module, then call as shown in the
examples.

Function Agecount6(ByVal pdob As Date, _
Optional ByVal pEdte As Variant, _
Optional ByVal pWhat As Variant) As String

'*****************************************************
'Purpose: Display age or difference between
' two dates with options to display
' in any variation of years, months,
' days.
'Coded by: raskew
'Inputs: 1) ? Agecount6(#3-Mar-80#) 'defaults
' to current date & "ymd" display
'
' 2) ? Agecount6(#3-Mar-80#, "4/25/04")
' Uses PEdte in place of date(),
' and default "ymd" display

' 3) ? Agecount6(#3-Mar-80#, "4/25/04", "d")
' Same as 2), but with display as days
'
'Output: 1) 29 years, 2 months, 10 days
' 2) 24 years, 1 month, 22 days
' 3) 8819 days
'*****************************************************

Dim dte2 As Date
Dim dteMyDate As Date
Dim intHold As Integer
Dim n As Integer
Dim strHold As String
Dim strHold2 As String
Dim strTemp As String
Dim strWhat As String

strWhat = IIf(IsMissing(pWhat), "ymd", pWhat)

dteMyDate = pdob
dte2 = IIf(IsMissing(pEdte), Date, pEdte)
For n = 1 To Len(strWhat)
strHold = Mid(strWhat, n, 1)
Select Case strHold

Case "y"
intHold = DateDiff("yyyy", dteMyDate, dte2) + _
(dte2 < DateSerial(year(dte2), month(dteMyDate), day
(dteMyDate)))
dteMyDate = DateAdd("yyyy", intHold, dteMyDate)
strHold2 = strHold2 & LTrim(str(intHold)) & " year" & IIf
(intHold <> 1, "s, ", ", ")

Case "m"
intHold = DateDiff("m", dteMyDate, dte2) + (day(dteMyDate) > day
(dte2))
dteMyDate = DateAdd("m", intHold, dteMyDate)
strHold2 = strHold2 & LTrim(str(intHold)) & " " & "month" & IIf
(intHold <> 1, "s, ", ", ")

Case "d"
intHold = DateDiff("d", dteMyDate, dte2)
strHold2 = strHold2 & LTrim(str(intHold)) & " " & "day" & IIf
(intHold <> 1, "s", "")

End Select
Next n

Agecount6 = strHold2

End Function

HTH - Bob

I am trying to calculate the age of employees on the date of the injury...not
just by the year. I have been trying this formula...

=DateDiff("yyyy",[DOB],[Date of Injury])+(Format([DOB],"mmdd")>Format[Date
of Injury],"mmdd"))

any suggestions?

 

[John W. Vinson]

I am trying to calculate the age of employees on the date of the injury...not
just by the year. I have been trying this formula...

=DateDiff("yyyy",[DOB],[Date of Injury])+(Format([DOB],"mmdd")>Format[Date
of Injury],"mmdd"))

any suggestions?

You want the age in the form 31 years, 2 months, 6 days? If so try Graham
Seach's Diff2Dates function as posted by Doug Steele:

http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[MGFoster]

I am trying to calculate the age of employees on the date of the injury...not
just by the year. I have been trying this formula...

=DateDiff("yyyy",[DOB],[Date of Injury])+(Format([DOB],"mmdd")>Format[Date
of Injury],"mmdd"))

any suggestions?

You want the age in the form 31 years, 2 months, 6 days? If so try Graham
Seach's Diff2Dates function as posted by Doug Steele:

http://www.accessmvp.com/djsteele/Diff2Dates.html

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe the poster just wants the person's age in years on a specific
date. The following formula works for me (though it relies on
True = -1):

Year(Date())-Year(birthdatefield) + (DateSerial(Year(Date()),
Month(birthdatefield), Day(birthdatefield))>Date())

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSguI8YechKqOuFEgEQIlfQCghQmaquR5KqMYiLq9enwSXnS/wtAAoI/9
bLwqYUrYKUSX1Eo/3hGoRR7r
=KahB
-----END PGP SIGNATURE-----