If person "A" is 10 years and 11 months old (10.11) and person "B" is 9 years and 8 months old (9.8), the difference in their ages is 15 months or 1 year and 3 months (1.3).

I'm having trouble figuring the formula for this. Dates of birth are not available. Anyone know how to build a calculation where I can determine the age difference expressed as a decimal (1.3) or using a dash (1-3)?

Decimal

Person A: 10.11

Person B: 9.8

________________

Difference 1.3

Dash

Person A: 10-11

Person B: 9-8

________________

Difference 1-3

Sorry if I'm not being clear. I just want to be able to calculate the difference between two fields with age equivalent values and have the result/difference appear in a third (result) field. TIA LM

FMP8A on Win XP

You haven't made the format of the age completely clear, and / or whether that format is ALWAYS constant i.e.

11 years and 2 months

12 years and 1 months

1 years and 6 months

1 years and 1 months

63 years and 8 months

19 years and 1 months

Is the above, the format you get it in?

The format is [years-months] so that 10 years, 3 months is expressed as "10-3". The age fields are in the same record.

But I cannot figure how to write a calculation that will convert the years and months to total months for person "A" (131) and total months for person "B" (116), then subtract the total months for "B" from total months for "A" then convert the difference (15) back to years and months (1-3). Any ideas? Thank you. LM

You certainly picked a difficult format to work with. Try something like:

Let ( [

a = Substitute ( PersonA ; "-" ; " " ) ;

ya = LeftWords ( a ; 1 ) ;

ma = MiddleWords ( a ; 2 ; 1 ) ;

b = Substitute ( PersonB ; "-" ; " " ) ;

yb = LeftWords ( b ; 1 ) ;

mb = MiddleWords ( b ; 2 ; 1 ) ;

c = 12*ya + ma - 12*yb - mb

] ;

Int ( c / 12 ) & "-" & Mod ( Abs ( c ) ; 12 )

)

Edited October 10, 200619 yr by Guest

Hi comment

this little modification of your calculation can evaluate better if the PersonB is older than PersonA:

Let ( [

a = Substitute ( PersonA ; "-" ; " " ) ;

ya = LeftWords ( a ; 1 ) ;

ma = MiddleWords ( a ; 2 ; 1 ) ;

b = Substitute ( PersonB ; "-" ; " " ) ;

yb = LeftWords ( b ; 1 ) ;

mb = MiddleWords ( b ; 2 ; 1 ) ;

c = Abs ( 12*ya + ma - 12*yb - mb )

] ;

Case(

c ; Div ( c ; 12 ) & "-" & Mod ( c ; 12 );

"they have the same age"

)

)

I am not sure if that is the requirement here, but in any case my calc did not handle negative results correctly, so I have edited it slightly.

Of course, the true solution is to have one field for months, and (optionally) another for years.

I am very grateful for all of your advice, Thank you. Now, if person "A" (9-10) is older than person "B" (8-1), I'd like the result/difference to be expressed as a negative in parentheses "(1-9)". How do I add that to the calc or is there a better way? Thank you again. LM

You could do it this way:

Let ( [

a = Substitute ( PersonA ; "-" ; " " ) ;

ya = LeftWords ( a ; 1 ) ;

ma = MiddleWords ( a ; 2 ; 1 ) ;

b = Substitute ( PersonB ; "-" ; " " ) ;

yb = LeftWords ( b ; 1 ) ;

mb = MiddleWords ( b ; 2 ; 1 ) ;

c = 12*yb + mb - 12*ya - ma

] ;

Case (

c < 0 ;

"(" & Div ( - c ; 12 ) & "-" & Mod ( - c ; 12 ) & ")" ;

Div ( c ; 12 ) & "-" & Mod ( c ; 12 )

)

)

And yes, I already said there is a better way - not to create the problem to begin with.

Thank you, Comment! That worked great. I had no choice in the format since I get pre formatted data from a publisher and need to import as such and adhere to their format. Thanks again! lm