Date code needed

  • Thread starter Thread starter terry w
  • Start date Start date
T

terry w

greetings

I need help writing code to determine a student's AgeOfRecord. Given a
student's birthday (DoB) and a Effective Date (DateEff), the AgeOfRecord is
found like this:

if DateEff is Jul 01 - Dec 31, then AgeOfRecord = student's age on Jan 01 of
the NEXT year
if DateEff is Jan 01 - Jun 30, then AgeOfRecord = student's age on Jan 01 of
the SAME year

TQ in advance for assistance
Terry W
 
greetings

I need help writing code to determine a student's AgeOfRecord. Given a
student's birthday (DoB) and a Effective Date (DateEff), the AgeOfRecord is
found like this:

if DateEff is Jul 01 - Dec 31, then AgeOfRecord = student's age on Jan 01 of
the NEXT year
if DateEff is Jan 01 - Jun 30, then AgeOfRecord = student's age on Jan 01 of
the SAME year

TQ in advance for assistance
Terry W
Click to expand...

Year([DateEff]) - Year([DateOfBirth]) + IIF(Month(DateEff)<7, 1, 0)
 
John - thanks for your prompt reply. I think the "+" before the IIf should
be a "-".

AgeOfRecord = Year([DateEff]) - Year([DateOfBirth]) - IIF(Month(DateEff)<7,
1, 0)

This formula seems to work well, but I'm puzzled by the formula's behavior
when DoB is January 1.

Let's say a student is born on Jan 1, 2000 and the DateEff is March 31,
2008. On Jan 1, 2008, the student would be 8 years old. Since the DateEff
is in the first half of the year, the student's AgeOfRecord is her age on Jan
1, 2008, namely 8. But, the formula picks 7.

All other date combinations seem to work fine. The only problem seems to be
with the 'boundary condition', January 1.

Thanks
Terry W

John W. Vinson said:
greetings

I need help writing code to determine a student's AgeOfRecord. Given a
student's birthday (DoB) and a Effective Date (DateEff), the AgeOfRecord is
found like this:

if DateEff is Jul 01 - Dec 31, then AgeOfRecord = student's age on Jan 01 of
the NEXT year
if DateEff is Jan 01 - Jun 30, then AgeOfRecord = student's age on Jan 01 of
the SAME year

TQ in advance for assistance
Terry W
Click to expand...

Year([DateEff]) - Year([DateOfBirth]) + IIF(Month(DateEff)<7, 1, 0)
Click to expand...
 
Back
Top