Age at the time of a specific event (that does not recalculate)

[4Justme2]

I need help with a formula to capture a person's age at the time of a specific event and have that specific age remain in history without recalculating. Is there anyone out there that can help me?

[Vaughan]

How do you want the "age" displayed?

[comment]

Try =

Year ( DateOfEvent ) - Year ( DateOfBirth ) - ( DateOfEvent < Date ( Month ( DateOfBirth ) ; Day ( DateOfBirth ) ; Year ( DateOfEvent ) ) )

[4Justme2]

How do you want the "age" displayed?

i.e. someone born 1 year ago I want the result to = 1

i.e. someone born 3 months ago I want the result to be .03

Try =

Year ( DateOfEvent ) - Year ( DateOfBirth ) - ( DateOfEvent < Date ( Month ( DateOfBirth ) ; Day ( DateOfBirth ) ; Year ( DateOfEvent ) ) )

Not sure if I did something wrong with this but the result I got was -1998. The DateOfBirth for this particular person was 7/14/1998, DateOfEvent 9/3/2012.

I entered the following calculation: Year ( DateOfEvent ) - Year ( _PEOPLE::DOB ) - ( DateOfEvent < Date ( Month ( _PEOPLE::DOB ) ; Day ( _PEOPLE::DOB ) ; Year ( DateOfEvent ) ) )

Any ideas what my problem is with this? I was hoping for a result of 14 (or more accurately 14.08 which includes mos)

[comment]

A result of -1998 indicates that the Date of Event is empty.

I was hoping for a result of 14 (or more accurately 14.08 which includes mos)

Try =

Let ( [

m = 12 * Year ( DateOfEvent ) + Month ( DateOfEvent ) - 12 * Year ( DateOfBirth ) - Month ( DateOfBirth ) - ( Day ( DateOfEvent ) < Day ( DateOfBirth ) )

] ;

Div ( m ; 12 ) & SerialIncrement ( ".00" ; Mod ( m ; 12 ) )

)

Note that this will return "14.01" in your example - which IMHO is the correct result.

[4Justme2]

A result of -1998 indicates that the Date of Event is empty.

Hmmmm, the field is polpulated so maybe I have something else wrong with that field? I wonder if it has anything to do with the fact that the person and date both is coming form the related parent table...

I tried implementing the revised calc above:

Let ( [m = 12 * Year (DateOfEVent ) + Month ( DateOfEVent ) - 12 * Year ( _PEOPLE::DOB ) - Month ( _PEOPLE::DOB ) - ( Day ( DateOfEVent ) < Day ( _PEOPLE::DOB ) )] ;Div ( m ; 12 ) & SerialIncrement ( ".00" ; Mod ( m ; 12 ) ))

But am still getting bogus result. Would you mind taking a look at the attached?

AgeAtDateOfEvent.zip

[doughemi]

You don't have the Do Not Replace checkbox unchecked in the auto enter field.

[comment]

Why don't you use a calculation field? True, it will be unstored because it references a related field, but it will recalculate if the DOB is ever modified - which is a good thing, IMHO.

[4Justme2]

You don't have the Do Not Replace checkbox unchecked in the auto enter field.

Good point, missed that.

Why don't you use a calculation field? True, it will be unstored because it references a related field, but it will recalculate if the DOB is ever modified -which is a good thing, IMHO.

That's an excellent idea--I'll definitely make that change. But did you notice that my calculation is sitll coming out wrong? In the very last column on the first record... Julio's age is calculating as 2012.09.. he's just a hair over 37. Can anyone tell what it is I've done wrong?

[comment]

Your "calculation" is not wrong - it was correct at the time it was performed (when the record was created). Now it's stored and will not be re-evaluated again.

[4Justme2]

Your "calculation" is not wrong - it was correct at the time it was performed (when the record was created). Now it's stored and will not be re-evaluated again.

My issue wasn't that it won't re-evaluate, it was the initial result of 2012.09. The result should be an age. If the calcualtion is correct what is it that I have wrong that the result doesn't provide me with an age?

[comment]

It's a matter of timing. Currently, the calculation evaluates before the relationship is established, so the related DOB is not yet available. I suggest you make the following experiment: allow entry to the field, clear the field contents and re-enter the DateOfEvent. You will see that as the field's calculation re-evaluates, it will produce the correct result.

Of course, all this won't be necessary if you change the field's type to Calculation (or at least uncheck the 'Do not replace...' option).

[4Justme2]

Oh, I see what you mean. Works like a charm! Many many thanks... mp :yep:

[LaRetta]

This also produces incorrect result 2012.10 if the birth date is empty. You might want to wrap with:

Case ( PEOPLE::DOB ; ... your calc ... )

In fact, they both should be trapped although I would assume there is always an activity date.

Case ( PEOPLE::DOB and DateOfEvent ; ... your calc ... )