Setting age using dates

[Sabrina]

OK, I can't find somewhere that it's already posted how to do this . . .

I'm trying to automatically set the age of a person on a set day by entering their date of birth. The calculation I figured out that isn't working is:

Focus Group::Focus_Group_Date- Respondent_Date_of_Birth

I know why it isn't working -because I'm subtracting the serial number assigned to the date and not the number date, but I can't figure out an alternative. I tried searching for a previous post, but I can't find one. Can anyone point me in the right direction or help me come up with a formula that works?

Thanks all!

[kenneth2k1]

This is a simple age calc. I have modified it to fit your fields. So try it and see if it works. Make sure calc result is number.

Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth) - (Focus Group::Focus_Group_Date < Date(Month(Respondent_Date_of_Birth), Day(Respondent_Date_of_Birth), Year(Focus Group::Focus_Group_Date))

[Sabrina]

Thank you for yor response! You typed:

Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth) - (Focus Group::Focus_Group_Date < Date(Month(Respondent_Date_of_Birth), Day(Respondent_Date_of_Birth), Year(Focus Group::Focus_Group_Date))

Unfortunately, this equasion didn't quite work even after I played with it. It's closer than what I had, but still not right. Say the Focus group was today, July 23, 2003 and the person was born August 22, 1967. The equasion would look something like:

2003-1967-(serial number of July 23, 2003) < (serial number of August 22, 1967)

right?

What if I did an if statement . . .

If (Resp month & day > Focus group month & day)

(year(FocusGroup) -1) - year(Resp D.O.B.)

Else

year(FocusGroup) - year(Resp D.O.B.)

End If

I'm going to go try this . . . wish me luck!

[Sabrina]

This is working perfectly! Thanks for setting me on the right track!

If(Month(Respondent_Date_of_Birth) and Day(Respondent_Date_of_Birth) > Month(Focus Group::Focus_Group_Date) and Day(Focus Group::Focus_Group_Date), Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth) - 1, Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth))

P

[kennedy]

Sabrina, look again at what Kenneth wrote... the only problem I see with it is that it needed one extra paren at the end.

Its not some weird serial number math as you suggested:

2003-1967-(serial number of July 23, 2003) < (serial number of August 22, 1967)

rather it is:

2003-1967 - '1 if your birthday this year hasn't come yet; 0 otherwise'

Now many would propose an if expression for that part in quotes:

if('your birthday this year hasn't come yet', 1, 0)

but consider that a Boolean test returns 1 if true or 0 if false,

so you can just subtract the result of the test. So, how do you

do the test?

'today < your birthday this year' == 'your birthday this hasn't come yet'

or:

Today < Date(Month(Birthdate), Day(Birthdate), Year(Today))

of course, in your case we are using Focus_Group_Date instead of Today.

Now the latter formula that you gave makes no sense: (sorry )

If(Month(Respondent_Date_of_Birth) and Day(Respondent_Date_of_Birth) > Month(Focus Group::Focus_Group_Date) and Day(Focus Group::Focus_Group_Date), Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth) - 1, Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth))

Note that 'and' has Booleans left and right. So, it first looks at Month(...) as a Boolean... since it will always be non-zero (there is no 0th month), it is always true... so we can just drop that. Same is true of Day(...). So, that leaves us with:

If(1 and Day(Respondent_Date_of_Birth) > Month(Focus Group::Focus_Group_Date) and 1, Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth) - 1, Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth))

So, if the day of the person's birth is greater than the focus group month, then you subtract one. That's kinda silly.

I recommend you go with what Kenneth suggested (with an extra paren at the end).

[Sabrina]

The equasion I wrote is actually working perfectly. I went through tons of dates and it was always correct. I'll go test it some more, but it seems to be fine.

Thanks!

[Sabrina]

I also fixed his equasion with the paren before and it did not help. I kept getting 1 for the ages. *Shrug*

[Sabrina]

OK, I see where my equasion didn't make sense, so I corrected it.

Case(Month(Respondent_Date_of_Birth) > Month(Focus Group::Focus_Group_Date), Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth) - 1, Month(Respondent_Date_of_Birth) = Month(Focus Group::Focus_Group_Date) and Day(Respondent_Date_of_Birth) > Day(Focus Group::Focus_Group_Date), Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth) - 1, Year(Focus Group::Focus_Group_Date) - Year(Respondent_Date_of_Birth))

I know this seems like a lot of work, but the other equasion wasn't working and it is vital the age be exactly correct.

[Ugo DI LUCA]

Why use so many Case statement where what you need is just an extra parnthesis to Ken's Calc ?

Should we call this obstination ?

[kenneth2k1]

Well, she did say that she tried it and it doesn't work. There may be something with the related dates. Who knows?

No matter how it works, as long as it works, right?

PS - The date calc I used was actually one that Kennedy had posted a long time ago, but he probably doesn't remember it. It was so easy to just copy/paste the calc, I use it for every age calc that I have. Sorry I missed the last parenthesis. That's what I get for smokin and typin!

Ken

[kennedy]

Hmmm... are your fields of type Date or type Text? If Text, then by consistently using the Month/Day/Year functions you auto-convert the Text to Date each time... but in the shorter formula the "<" and ">" tests will not do a date test... they will do an alphanumeric Text sort test... which of course will yield bizarre answers. Just one thought on why the formula might not have worked for you.