Date function help needed

[Guest]

greetings to all

I need help with code for a function. I'm getting close but keep messing
up. My sports league determines the 'competing age' of its athletes, given
their DOB, as follows:

If TODAY's date is from Jan 1 to Jun 30, then
CompAge = the kid's age on Jan 1 of the CURRENT year.
If TODAY's date is from Jul 1 to Dec 31, then
CompAge = the kid's age on the FOLLOWING Jan 1.

 

[Guest]

Do you need just years or do you need more of a breakdown year months days...

here are two good postings on the subject you should be able to get what you
need

http://groups.google.ca/group/comp....read/thread/4c02f7b7b44727e4/d1a2c6cdb0b96309
http://groups.google.ca/group/micro...read/thread/e53c5420f5e32625/395a8cc421c6c2aa

Daniel

 

[raskew via AccessMonster.com]

Hi Sophie:

The following function will return Age in yy.mm.dd format:
************************************************************************************************************
Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
'*******************************************
'Purpose: Accurately return the difference
' between two dates, expressed as
' years.months.days
'Coded by: raskew
'Inputs: From debug (immediate) window
' 1) ? fAge(#12/1/1950#, #8/31/2006#)
' 2) ? fAge(#12/30/2005#, #1/2/2006#)
'Outputs: 1) 55.8.30
' 2) 0.0.3
'*******************************************

Dim intHold As Integer
Dim dayhold As Integer

'correctly return number of whole months difference
'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
'that returns -1 if true, 0 if false
intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))

'correctly return number of days difference
If Day(dteEnd) < Day(dteStart) Then
dayhold = DateDiff("d", dteStart, DateSerial(year(dteStart), month
(dteStart) + 1, 0)) + Day(dteEnd)
Else
dayhold = Day(dteEnd) - Day(dteStart)
End If

fAge = LTrim(str(intHold \ 12)) & "." & LTrim(str(intHold Mod 12)) & "." &
LTrim(str(dayhold))

End Function
************************************************************************************************************

Call it like this to toggle between this year and next year, based on whether
the current month is >= July (7)

? fage(#12/1/1950#, dateserial(year(date())+ abs(month(date())>=7),1,1))
56.1.0

HTH - Bob

 

[raskew via AccessMonster.com]

Oops!

Last line should have read:

? fage(#12/1/1950#, dateserial(year(date())+ abs(month(date())>=7),1,0))

Sure wish we had an Edit feature in this forum

Hi Sophie:

The following function will return Age in yy.mm.dd format:
************************************************************************************************************
Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
'*******************************************
'Purpose: Accurately return the difference
' between two dates, expressed as
' years.months.days
'Coded by: raskew
'Inputs: From debug (immediate) window
' 1) ? fAge(#12/1/1950#, #8/31/2006#)
' 2) ? fAge(#12/30/2005#, #1/2/2006#)
'Outputs: 1) 55.8.30
' 2) 0.0.3
'*******************************************

Dim intHold As Integer
Dim dayhold As Integer

'correctly return number of whole months difference
'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
'that returns -1 if true, 0 if false
intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))

'correctly return number of days difference
If Day(dteEnd) < Day(dteStart) Then
dayhold = DateDiff("d", dteStart, DateSerial(year(dteStart), month
(dteStart) + 1, 0)) + Day(dteEnd)
Else
dayhold = Day(dteEnd) - Day(dteStart)
End If

fAge = LTrim(str(intHold \ 12)) & "." & LTrim(str(intHold Mod 12)) & "." &
LTrim(str(dayhold))

End Function
************************************************************************************************************

Call it like this to toggle between this year and next year, based on whether
the current month is >= July (7)

? fage(#12/1/1950#, dateserial(year(date())+ abs(month(date())>=7),1,1))
56.1.0

HTH - Bob

greetings to all

[quoted text clipped - 6 lines]

If TODAY's date is from Jul 1 to Dec 31, then
CompAge = the kid's age on the FOLLOWING Jan 1.