Age calculation

[SteveS]

I cannot seem to find my notes relating to an age calculation formula. I have layout containing a (Date of Birth) "date field" and an (Age) "calculation field." Can anyone tell me how to make the Age field display an individual's current age?

[RalphL]

Do a search on " Age " use the quotes, for the last 2 months.

[SteveS]

Ralph,

Thank you for your help.

(Get(CurrentDate)-DOB)/365.2425

[RalphL]

I was hoping you would use a better one than that something like:

Let ( [ C = Get ( CurrentDate ) ;

yC = Year ( C ) ;

B = Birthday ;

yB = Year ( B ) ;

mB = Month ( B );

dB = Day ( B ) ] ;

yC - yB - (C < Date ( mB; dB; yC ) ))

[kevh72]

This simple calc works:

Year ( Get ( CurrentDate ) - Birthdate ) -1 )

[kevh72]

plus, remember to unstore the calc.

[-Queue-]

That doesn't work if today is your birthday.

You have to add a test for today.

Year(Get(CurrentDate) - Birthdate) - 1 + (Date( Month(Birthdate); Day(Birthdate); Year(Get(CurrentDate)) ) = Get(CurrentDate))

[comment]

Oh, no - again?

I am sorry, but that will not work.

What is (Get(CurrentDate) - Birthdate)?

It is the number of days elapsed from birthdate until today - let's call it n.

What is Year(Get(CurrentDate) - Birthdate)?

It is the year of the date on which n days have elapsed since Jan 1 0001 - let's call it y. For a person less than 100 years old, this year is somewhere in the first century C.E..

If Birthday is in Year1 and today is Year2, the calc will be correct only if the number of leap years in the period Year1 - Year2 is equal to the number of leap years in the period 0001 - y. There's no way to assure that.

Examples of dates that break the calc:

Queue:

Birthdate = 2/2/2000

Today = 2/2/2001

kevh72:

Birthdate = 2/2/2000

Today = 2/2/2004

[-Queue-]

I knew I should have tested more thoroughly, though I didn't have the time. I like the idea though, even if the execution doesn't quite cut it.

[LaRetta]

Learned from Ray (CobaltSky) ... and I've NEVER had it break!

Let([n = Get(CurrentDate); b = DOB];

Year(n) - Year( - ((Month(n) + Day(n) / 100) < (Month( + Day( / 100))

)

For the thread, see here.

Comment, thank you for sharing your 'thinking through' process on this. It helps me learn the principle behind what works and what doesn't! Date calculations fascinate (and frustrate) me.

LaRetta

[kevh72]

My solutions do not require that it be accurate to the month in any way; therefore, my calc only requires age to be determined from 1 on. For example, anyone born less than 1 year to the current date is listed as 0 year of age. But for anyone born over a year ago, the calc accurately determines age. I have thoroughly tested the calc and it works just fine. I'm running filemaker 7 and it does take into account for leap years. For example, you can't type in 2/29/05, but you can for 2/29/04, but that doesn't truly matter for this calc to work.

Try it out before you say it doesn't work.

[kevh72]

Sorry about the post. You guys are correct about the century thing, but because my solutions don't log people born after 2000, my calc worked without any breaks.

[kevh72]

Thanks for the post, I'll definitely use it since I'd rather have a flawless calc than one that can break in some instances. This forum is great!

[comment]

LaRetta:

Ray's calc is essentially the same as the one suggested by Ralph. IMHO it adds an unnecessary complication, but it doesn't affect the result.

It is fascinating what people will come up with. After all, we all know our age without a computer, don't we?

[comment]

It is not a century thing. It's about aligning leap years. If you like, I can give you an example that will break it in the previous century as well.

[LaRetta]

Oh yes, Comment, again ...

Comment said ...After all, we all know our age without a computer, don't we?

This is more important than simply knowing one

[RalphL]

There is nothing wrong with Ray's calculation or the one I posted in this thread. The problem was the one posted by kevh72.

[LaRetta]

Hi Ralph

I was basing my question more on Comment's statement about both Ray's and your calculations ... "IMHO it adds an unnecessary complication, but it doesn't affect the result. "

"... doesn't affect the result" appears to mean even Ray's calculation 'fails' in some situations. I have a hard time believing that Ray would add an unnecessary complication to ANY of his calculations so I've interpreted the problem more that FM can't always properly align leap years!! If true, I want to understand it.

Did I incorrectly interpret this statement? I hope I did because I can then lay this to rest and trust my adopted 'age' calculation. Until then, I will simply go nuts trying to figure it out.

LaRetta

[RalphL]

My calculation:

Let ( [ C = Get ( CurrentDate ) ;

yC = Year ( C ) ;

B = Birthday ;

yB = Year ( B ) ;

mB = Month ( B );

dB = Day ( B ) ] ;

yC - yB - (C < Date ( mB; dB; yC ) ))

Ray's calculation:

Let([n = Get(CurrentDate); b = DOB];

Year(n) - Year( - ((Month(n) + Day(n) / 100) < (Month( + Day( / 100))

)

Both use the difference in years with a correction for the case when the birthday for the current year has occured. Mine use the dates of the current date and the birthday in the current year. Ray uses a number based on the month and day, i.e., today 3/5/2005 would be 3.05. A brithday before today would give a smaller number, while a latter birthday would give a larger number.

I think comment was refering to the correction part of the calculation.

[comment]

All right, then - walk with me:

 Let ( [



// I was born on Birthdate, and it is now Today. How old am I?

// I will turn x years old this year:



x = Year (Today) - Year (Birthdate) ;



// But I will only turn x years old on my birthday this year, right?

// Until my birthday actually occurs, I will be only (x - 1) years old.

// So, when is my birthday this year?



thisBirthday = Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year (Today ) ) ;



// Has my birthday already occured this year?



subtract1 = Today < thisBirthday 



// Note that subtract1 equals 1 if my birthday has NOT yet occured, otherwise 0. 



] ;



// So now we have all the info we need, and can do the math:

// My age today is:



x - subtract1

) 

 





This is of course not that complicated, so we can dispense with the Let() and write directly:

Age =

Year ( Get ( CurrentDate ) ) - Year ( Birthdate ) - ( Get ( CurrentDate ) < Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year ( Get ( CurrentDate ) ) ) )

 



Ray's calc does exactly the same thing, except his method for checking if my birthday has already occured this year is:

 Month ( Today ) + Day( Today ) / 100 ) < ( Month ( Birthdate ) + Day( Birthdate ) / 100) 

What this does is convert the birthday to a decimal number in the format of Month.Day, so for example March 5 is 3.05 (3 + 5/100) and December 25 is 12.25 (12 + 25/100). Then the comparison is:

3.05 < 12.25

and this of course returns 1, since December 25 has not yet occured.

So don't expect to break Ray's calc, because you can't. The only thing that can be said against it is that it is not as readable as the first one. It needs to be explained, while "our" calc just follows what you'd normally do.

Now for the leap years thing:

FileMaker does know about leap years, and it does NOT "fail to properly account for leap years in some instances". But some calculations do, and the one that was suggested here is an example.

The calc is actually quite a neat trick. It says: if I was born on January 1, 0001 and lived the same number of days as I actually did - what year would it be then? That is my age today.

The problem is this: Suppose I was born January 1, 1996 and today is Dec 31, 1996. Because 1996 was a leap year, so far I have lived 365 days, but I have not turned 1 yet. That will only happen tomorrow, January 1, 1997.

But 0001 was NOT a leap year. So if you take 365 days from January 1, 0001, you are already in January 1, 0002, and suddenly I am 1 year old.

This is like measuring a piece of string. Normally, you'd measure my age from my birthday to today. The calc says: let's take a short cut and measure it from the 0 mark on the ruler. But the ruler is not uniform. It expands and contracts according to leap years. So unless you align the leap years in your measurement, you cannot guarantee accuracy.

This concludes Age101. Whew!

[LaRetta]

If Comment was referring only to the correction portion of the calculation and, since the correction changes the result, then the integrity of the entire date calculation would be in question. But I didn't feel he was only talking about the boolean adjustment; rather, on a broader scale, FM's inability (?) to cross leap-year boundaries properly in ALL of our date calculations.

I hope Comment can put me out of my misery on this issue.

Edited ...

Whew is right!! WOW!! This is great stuff, Comment!! YES!!!

Okay, I'll crawl back to my hole with all this information and pick it apart!!!

Comment! Thank you for taking the time to explain it!!

[comment]

I try, but you don't give me time.

[LaRetta]

sorry. i get worked up and excited over all of this FM stuff. A passion ...

[transpower]

But the above just gives you the age in years. If you want months and days, as well, use this:

Let ( DateNow = Get ( CurrentDate ) ;

GetAsText ((Year (DateNow) - Year (birthday) - If (DateNow < Date (Month (birthday); Day (birthday); Year (DateNow)); 1; 0)) & " Years, " &

GetAsText (Mod (Month (DateNow) - Month(birthday) + 12 - If (Day (DateNow) < Day (birthday); 1; 0); 12)) & " Months, " &

GetAsText (Day (DateNow) - Day (birthday) + If (Day (DateNow) >= Day (birthday); 0; If (Day (DateNow - Day (DateNow)) < Day (birthday); Day (birthday); Day (DateNow - Day (DateNow)))))) & " Days" )

[Rob 7 Collins]

LaRetta, since you were interested in where FileMaker's date system may not be properly accurate, you might enjoy this.

Usually, people who would have a use for old dates, like historians, have to account for the convertion between the gregorian calendar and the Julian calendar if the span of date dates in the comparison bridges the date 10/15/1582. This is because the standard is to use the old Julian calendar for dates when it was in use.

This actually is important to anyone who would like to use dates when referring to historical events, like the following (thank you Wikipedia!):

If comparisons of dates are done using different calendars, we can encounter logical absurdities such as William and Mary of Orange seeming to arrive in London to accept the English crown, a week or so before they left the Netherlands; and Shakespeare and Cervantes apparently dying on exactly the same date, when in fact Cervantes predeceased Shakespeare by 10 days in real time.

[Rob 7 Collins]

LaRetta, since you were interested in where FileMaker's date system may not be properly accurate, you might enjoy this.

Usually, people who would have a use for old dates, like historians, have to account for the convertion between the gregorian calendar and the Julian calendar if the span of date dates in the comparison bridges the date 10/15/1582. This is because the standard is to use the old Julian calendar for dates when it was in use.

This actually is important to anyone who would like to use dates when referring to historical events, like the following (thank you Wikipedia!):

If comparisons of dates are done using different calendars, we can encounter logical absurdities such as William and Mary of Orange seeming to arrive in London to accept the English crown, a week or so before they left the Netherlands; and Shakespeare and Cervantes apparently dying on exactly the same date, when in fact Cervantes predeceased Shakespeare by 10 days in real time.

[Rob 7 Collins]

LaRetta, since you were interested in where FileMaker's date system may not be properly accurate, you might enjoy this.

Usually, people who would have a use for old dates, like historians, have to account for the convertion between the gregorian calendar and the Julian calendar if the span of date dates in the comparison bridges the date 10/15/1582. This is because the standard is to use the old Julian calendar for dates when it was in use.

This actually is important to anyone who would like to use dates when referring to historical events, like the following (thank you Wikipedia!):

If comparisons of dates are done using different calendars, we can encounter logical absurdities such as William and Mary of Orange seeming to arrive in London to accept the English crown, a week or so before they left the Netherlands; and Shakespeare and Cervantes apparently dying on exactly the same date, when in fact Cervantes predeceased Shakespeare by 10 days in real time.

[comment]

It depends on the country, e.g. Russia/Soviet Union made the transition only in 1918 - so they celebrated the October Revolution in November...

But that doesn't mean that FileMaker's date system is not "properly accurate". It is accurate within the Gregorian calendar. One only needs to remember that many historical dates are given in the Julian calendar, and make the conversion when necessary.

[comment]

It depends on the country, e.g. Russia/Soviet Union made the transition only in 1918 - so they celebrated the October Revolution in November...

But that doesn't mean that FileMaker's date system is not "properly accurate". It is accurate within the Gregorian calendar. One only needs to remember that many historical dates are given in the Julian calendar, and make the conversion when necessary.

[comment]

It depends on the country, e.g. Russia/Soviet Union made the transition only in 1918 - so they celebrated the October Revolution in November...

But that doesn't mean that FileMaker's date system is not "properly accurate". It is accurate within the Gregorian calendar. One only needs to remember that many historical dates are given in the Julian calendar, and make the conversion when necessary.

[Rob 7 Collins]

"It is accurate with the Gregorian calendar"

well, almost. The Gregorian calander has no date 10/12/1582

That is the proleptic gregorian calendar, which also has a 0AD date (and thus is wrong for BC dates).

[Rob 7 Collins]

"It is accurate with the Gregorian calendar"

well, almost. The Gregorian calander has no date 10/12/1582

That is the proleptic gregorian calendar, which also has a 0AD date (and thus is wrong for BC dates).

[Rob 7 Collins]

"It is accurate with the Gregorian calendar"

well, almost. The Gregorian calander has no date 10/12/1582

That is the proleptic gregorian calendar, which also has a 0AD date (and thus is wrong for BC dates).

[comment]

The Gregorian calendar has dates starting at January 1, 0001. With negative numbers, it has dates for any day since the beginning of time till doomsday. The mere fact that these dates were not used before the calendar was introduced does not invalidate them.

Certainly, even with the Julian calendar, no Greek ever referred to the current year as 356 B.C., for example. So if you admit to BC dates in principle, you should not object to extending Gregorian dates backwards as well.

Call it proleptic, if you will - it's still the same calendar. IMHO, the term "proleptic" is important only in the context of discussing historical events that are traditionally associated with their Julian dates. However, it IS important to emphasize which calendar is being used, when there is room for ambiguity.

BTW, the Gregorian calendar has no year 0. The ISO 8601 calendar does.

[comment]

The Gregorian calendar has dates starting at January 1, 0001. With negative numbers, it has dates for any day since the beginning of time till doomsday. The mere fact that these dates were not used before the calendar was introduced does not invalidate them.

Certainly, even with the Julian calendar, no Greek ever referred to the current year as 356 B.C., for example. So if you admit to BC dates in principle, you should not object to extending Gregorian dates backwards as well.

Call it proleptic, if you will - it's still the same calendar. IMHO, the term "proleptic" is important only in the context of discussing historical events that are traditionally associated with their Julian dates. However, it IS important to emphasize which calendar is being used, when there is room for ambiguity.

BTW, the Gregorian calendar has no year 0. The ISO 8601 calendar does.