Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Automatic calculation of age using date of birth and current date


nrsmd
 Share

Recommended Posts

Sure.

 

Calc field, numerical, unstored.

 

Get(currentdate) - DateofBirth

 

 

(Assuming that DateofBirth is a Date field, as it should be)

Link to comment
Share on other sites

Fair enough.

 

Case(Month(Get(CurrentDate) ≥ Month(DateOfBirth),

Year(Get(CurrentDate) - Year(DateOfBirth),

Year(Get(CurrentDate)+1 - Year(DateOfBirth)

)

Link to comment
Share on other sites

I am afraid not - unless you are willing to settle for accuracy of +/- month.

And you are supposed to be a year older when your birthday passes - not younger.

See the calculation I have linked to.

Link to comment
Share on other sites

If you are working with neonates and toddlers, I find this calculation useful:

 

If(Get(CurrentDate)-DateofBirth=1;Get(CurrentDate)-DateofBirth & " " & "day";If ( Get ( CurrentDate ) - DateofBirth

Link to comment
Share on other sites

You would do well to update the calculation to the current technology: use Let() to define the difference between the two dates as a variable, instead of calculating the same thing 9 times, and Div ( a ; b ) instead of Int ( a / b ). And a single Case() is always preferable to multiple nested If()'s - but that's old news, dating back to at least version 4.

 

Other than that, I don't know what's the accepted standard in pediatrics, but the average month is not 30 days, and the average year is not 365.25.

Link to comment
Share on other sites

Originally Posted by AHunter3

Get(currentdate) - DateofBirth

 

I think "a person's age" means age in years, not in days.

 

 

OK, now that my brain isn't full of muscle relaxers... this is where it was trying to go yesterday —

 

(Get(currentdate) - Date of Birth) / 365.242199

 

 

or (since we want nice round number, otherwise why not express age in days?)... to handle the rounding in formula rather than field format on the layout:

 

Round( (Get(currentDate) - Date of Birth) / 365.242199, 0)

 

 

Not that there's anything wrong with the formula you linked to...

Link to comment
Share on other sites

Hi AHunter3 smiley-smile

 

Your calculation doesn’t account for the birthday not-yet passed during the current year. If a person is born on 8/1/2000 and the current date is 7/31/2006 (the day before their birthday), your calculation says they are 6.

 

As an aside … why go to the trouble of finding that number 365.242199? Do you keep it posted by your computer so it is handy to use whenever you need it? Does everyone (but me) keep that number handy? What happens when you are given two timestamps spanning 3,000 years and need to know the total days/seconds? Are you going to again manually calculate the average and then apply that fixed average number within a calculation? If you leave the dates (and/or times) alone, FileMaker will handle them accurately … give it a span (dates, times or timestamps) spanning year 1001-4000 and it will properly adjust for leap year and months of inconsistent length and will accurately calculate to the second if you ask it to.

 

We are not talking about a simple birthdate here but rather ANY date/time spans. Whether calculating a person’s birthday, radioactive carbon dating or a person’s retirement (which must be accurate to hours), accuracy DOES matter so why not use a principle that always works for ALL comparisons? If you do not, you will get twisted in your own logic and your calculations will break. Problem is … you won’t know your calculations break because you won’t know how to test it in any other way than your own incorrect logic that got you there. Instead, learn the principle of good date logic and then let FileMaker do the work for you.

 

LaRetta smiley-laughing

Link to comment
Share on other sites

Well, it all depends on the purpose. Computing the length of a period in average years can be appropriate in some circumstances. However, the average year in the Gregorian calendar is 365.2425 days long. 365.242199 is the average length of the tropical year. I don't think it's very relevant to a person's age.

Link to comment
Share on other sites

Hi LaRetta,

Yeah, I understand what you're saying, but the link doesn't solve my problem and my inelegant and inaccurate calculation does.

When I look at an operating list its not particularly helpful to see a childs age as 0. What is useful is to see whether it is 3days, 3weeks or 3months of age.

Dravid

Link to comment
Share on other sites

  • 1 year later...

2 Years later and in a different thread I posted this calculation:

 

Div ( Abs ( Date_A - Date_B ) ; 365 ) & " Year(s) " & Mod ( Abs ( Date_A - Date_B ) ; 365 ) & " Day(s)"

 

This works as a calculation set to text or as a custom function for a text field.

 

 

You can use any two dates, preferably entered via a date field, without worrying about which is older or what they stand for. The ABS automatically resolves the older/later problem.

 

Consider it years and days between two dates.

Link to comment
Share on other sites

:D you really going to dig up every old post about age calculation and suggest your solution? relax man....take it easy.

 

Since the threads lack a solution, I thought one might help since people will read these threads. Is that a problem?

 

Are you stalking me on every thread? smiley-wink

Link to comment
Share on other sites

Well... you're free to post wherever you like. I would think it safe to assume that a post that is over a year old somehow got resolved for the OP, even if it lacks a final answer (in this thread, the OP was answered; only a sideline was not fully resolved).

And I do think posting largely the same thing in three or four threads is a bit, well, overzealous and not entirely necessary and might look as if you're trying to open up a debate that's going on in a fresher thread already. That's why I posted. If that came across as stalking, I apologize.

Link to comment
Share on other sites

Div ( Abs ( Date_A - Date_B ) ; 365 ) & " Year(s) " & Mod ( Abs ( Date_A - Date_B ) ; 365 ) & " Day(s)"

 

This works as a calculation set to text or as a custom function for a text field.

 

 

You can use any two dates, preferably entered via a date field, without worrying about which is older or what they stand for. The ABS automatically resolves the older/later problem.

 

Consider it years and days between two dates.

 

Before employing Jack's technique, readers should consider the problems noted here:

http://www.filemakertoday.com/com/showthread.php?t=15129

 

and here:

http://www.filemakertoday.com/com/showthread.php?t=11918

 

and use one of the alternate techniques.

Link to comment
Share on other sites

date today = get (currentdate)

 

Age Yr =Year (Date Today) - Year (DOB) - If (Date Today

 

Age Month = Mod (Month (Date Today) - Month (DOB) + 12 - If (Day (Date Today)

 

Age Day = Day (Date Today) - Day (DOB) + If (Day (Date Today) >= Day (DOB); 0; If (Day (Date Today - Day (Date Today))

 

Use merge fields to diaplay as yy years, mm months, dd days.

 

> yrs,> months,> days

Link to comment
Share on other sites

Sujat, that's pretty neat. The logic makes sense. As far as I tested (only a few dates) it seems to work. One thing I found is a leapling is one year and one day older on march 1 of a non-leap year and 11months, 30 days on the 28th of februari. So I guess he skips his birthday three years out of four... Personally I have no problem with that (but I'm not a leapling) :)

Link to comment
Share on other sites

kjoe,

 

That's a good point. Thanks for pointing it out. Leap year didn't cross my mind and obviously I didn't test it. It has to be refined for those born on 29 Feb.

 

Sujat

Link to comment
Share on other sites

Yes there is good logic in this excepting the leap year issue noted above.

I would like to see the nested If not used and null results be non-explicit. Just my style! ;)

date today = get (currentdate)

 

Age Yr =Year (Date Today) - Year (DOB) - If (Date Today

 

Age Month = Mod (Month (Date Today) - Month (DOB) + 12 - If (Day (Date Today)

 

Age Day = Day (Date Today) - Day (DOB) + If (Day (Date Today) >= Day (DOB); 0; If (Day (Date Today - Day (Date Today))

 

Use merge fields to diaplay as yy years, mm months, dd days.

 

> yrs,> months,> days

Today = Get (CurrentDate) [unstored result]

 

Age Yr =

Year (Today) - Year (DOB) - 
If (Today < Date (Month (DOB); Day (DOB); Year (Today)); 1)

 

Age Month =

Mod(
   Month( Today ) - Month( DOB ) + 12 - If( Day( Today ) < Day( DOB ); 1 );
   12
)

 

Age Day =

Day( Today ) - Day( DOB ) +
Case(
   Day( Today ) >= Day( DOB ); 0;
   Day( Today - Day( Today ) ) < Day( DOB ); Day( DOB );
   Day( Today - Day( Today ) )
)

 

Note also an interesting piece of code in the day calculation:

Today - Day( Today )

This returns the last date of the previous month. For example, if Today is 14 September, it will return 31 August (14 Sept minus 14 days). Others have calculated this using:

Date ( Month (Today); 0; Year (Today) )

 

When wrapped in the Day function:

Day ( Today - Day( Today ) )

it returns the day (number) of the last date of the month before.

Link to comment
Share on other sites

  • 1 month later...

I think I used David's calculation to automatically fill out my age field, when I entered a date of birth in the Date of Birth field. As below:

 

Let ( [ today = Get ( CurrentDate ); refdate = Date of Birth]; Year ( today) - Year ( refdate ) - Case ( Month ( refdate ) > Month ( today ) or Month ( refdate ) = Month ( today ) and Day ( refdate ) > Day ( today ) ; 1))

 

Seems to work well too. I've not found a flaw with it yet.

Link to comment
Share on other sites

  • 2 years later...

I have looked at these posts, and various factors can cause false ages. So, I decided to work under the premise of first looking at the DOB (date of birth) as if it was in the same year as the current year. This will identify if the birthdate is today, in the past within this year, or in the future within this year. Then, based on whether it is =, , it will subtract the birth year from the current year (or subtract an extra year if the birth date this year falls before the current date this year). I wrote it out longhand so you can see three scenarios.

:)

Case (

DayOfYear ( Date ( Month (DOB); Day (DOB); Year ( Get (CurrentDate )))) = DayOfYear ( Date ( Month ( Get ( CurrentDate )); Day (Get ( CurrentDate )); Year (Get ( CurrentDate )))); Year ( Get ( CurrentDate ) ) - Year ( DOB );

DayOfYear ( Date ( Month (DOB); Day (DOB); Year ( Get (CurrentDate ))))

DayOfYear ( Date ( Month (DOB); Day (DOB); Year ( Get (CurrentDate )))) > DayOfYear ( Date ( Month ( Get ( CurrentDate )); Day (Get ( CurrentDate )); Year (Get ( CurrentDate )))); Year ( Get ( CurrentDate ) ) - Year ( DOB ) - 1)

Link to comment
Share on other sites

Update: Then, based on whether it is =, , it will subtract the birth year from the current year (or subtract an extra year if the birth date this year falls AFTER the current date ). I wrote it out longhand so you can see three scenarios.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use