1 2 3 Previous Next 32 Replies Latest reply on Feb 28, 2012 2:57 PM by errol.colautti

    Age (years, months, days) at death

    Gizmo

      I've tried to search for the answer to my question before posting, so if this has been asked and answered please point me in the right direction. I did find some information, but it was related to current date.

       

      I want calculate age in years, months and days based on birth and death. I provided a sample database.

      Birth: May 5, 2001

      Died: June 9, 2007

       

      My projects involves a cemetery, which is why using "current date" doesn't work.

       

      Thank you in advance.

       

      Connie

      (Mac OS X)

        • 1. Re: Age (years, months, days) at death
          StephenWonfor

          Connie

           

          Winfied Husllik has this one posted on Brian Dunnings CF site.   http://www.briandunning.com/cf/518

           

          // © 2006 Winfried Huslik, www.fmdiff.com

           

           

          Let ( [

              neg = Case ( date1 > date2 ; -1 ; 1 ) ;

              d1 = Case ( neg < 0 ; date2 ; date1 ) ;

              d2 = Case ( neg < 0 ; date1 ; date2 ) ;

           

           

              d = mod ( Day ( d2 ) - Day ( d1 ) ; Day (

                Date ( Month ( d1 ) + 1 ; 0; year ( d1 ) ) ) ) ;

           

           

              m = mod ( Month ( d2 ) - Month ( d1 ) -

                ( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ;

           

           

              y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -

                ( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )

            ];

              y * neg & ¶ &

              m * neg & ¶ & 

              d * neg & ¶ &

              y & " years, " & m & " months, and " & d & " days" & ¶

          )

          1 of 1 people found this helpful
          • 2. Re: Age (years, months, days) at death
            RayCologon

            Hi Connie,

             

            I'll take a look at your database, but meanwhile, you will get close to what you want to do with the following calculation:

             

            Let([

            B = DateOfBirth;

            P = DateOfDeath;

            yP = Year(P);

            mP = Month(P);

            dP = Day(P);

            mB = Month(B);

            dB = Day(B);

            e = Day(Date(mP; 0; yP));

            f = Min(dB; Day(Date(mP + 1; 0; yP)));

            a = If(dB > dP and dB > e; dB - e);

            y = yP - Year(B) - (P < Date(mB; Min(f; dB); yP));

            m = Mod(mP - mB - (dP < f); 12);

            d = P - Date(mP - (f > dP); If(f > dP; dB; f); yP) + a];

            Case(

            B and (P > B);

            Trim(

            If(y; y & Left(" years"; 6 - (y = 1))) &

            Case(y and m and d; ", "; y and m; " and ") &

            If(m; m & Left(" months"; 7 - (m = 1))) &

            If((m or y) and d; " and ") &

            If(d; d & Left(" days"; 5 - (d = 1)))

            );

            B and (P = B);

            "stillborn")

            )


            It references two fields; DateOfBirth and DateOfDeath, both of which are required to be Date fields, and it produces a text result such as:

             

            1 year, 2 months and 1 day

             

            I daresay the same logic can be adapted to work with the field formats in your example.

             

            Regards,

            Ray

            ------------------------------------------------

            R J Cologon, Ph.D.

            FileMaker Certified Developer

            Author, FileMaker Pro 10 Bible

            NightWing Enterprises, Melbourne, Australia

            http://www.nightwingenterprises.com

            ------------------------------------------------

             

            Edited by Ray Cologon: Formula revised to adjust offsets

            • 3. Re: Age (years, months, days) at death
              RayCologon

              gizmo wrote:

              ...I provided a sample database.

               

              Hi Connie,

               

              Okay, I've now taken a quick look at your file, and I'm attaching a modified version of it that contains appropriate calculation expressions (adapted from the above) to return the years, months and days as numbers, and the full age as a text string along the lines described in my previous post.

               

              Regards,

              Ray

              ------------------------------------------------

              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia

              http://www.nightwingenterprises.com

              ------------------------------------------------

               

              Edited by Ray Cologon: Example revised to include offset adjustments

              • 4. Re: Age (years, months, days) at death
                comment

                A person was born on April 30, 2011 and died on June 1, 2011. How long did they live?

                 

                One calculation says:

                • From April 30 till May 30: 1 month;
                • From May 30 till June 1: 2 days;

                1 month and 2 days

                 

                Another one says:

                • From April 30 till May 1: 1 day;
                • From May 1 till June 1: 1 month;

                1 month and 1 day

                 

                Which one is "correct"?

                • 5. Re: Age (years, months, days) at death
                  RayCologon

                  Michael Horak wrote:

                  Which one is "correct"?

                   

                  Hi Michael,

                   

                  Without context the question doesn't mean much - as abstract exercise, of course neither is inherently 'correct' - or 'incorrect'. Except insofar as it could be said that both should be expressed in equivalent fashion, so if you are going to measure elapsed time backwards from the date of death (as in your "Another one" method), for consistency and intelligibility, the order of dates should also appear backwards ie: from June 1 'til May 1; 1 month, from May 1 'til April 30: 1 day; 1 month and 1 day. Then at least the orientation matches the logic, as it does in the "One" method.

                   

                  That said, the measurement method is a matter of perspective, and it happens that the convention for expressing age (in our culture, at this time) is to count forward in increments from the time of birth, declaring the remainder in lower order units or discarding it depending on the expected precision.

                   

                  In this case, it seems the expected precision stops at days, so - no need to worry about hours, minutes, seconds or ticks (phew!), but for most purposes we stop at whole years.

                   

                  So, the calcs I offered (and, FWIW, also the one from Winfried that Stephen posted) work on model "One" with the time vector facing forward, in line with our subjective experience. In the (unlikely) event that Connie wants time to run backwards as in the "Another one" scenario, it will be a minor matter to adjust for that. ;)

                   

                  Regards,

                  Ray

                  ------------------------------------------------

                  R J Cologon, Ph.D.

                  FileMaker Certified Developer

                  Author, FileMaker Pro 10 Bible

                  NightWing Enterprises, Melbourne, Australia

                  http://www.nightwingenterprises.com

                  ------------------------------------------------

                  • 6. Re: Age (years, months, days) at death
                    comment

                    Ray Cologon wrote:


                    if you are going to measure elapsed time backwards from the date of death (as in your "Another one" method)

                     

                    Actually, this is an incorrect assumption (though it too fits my example). I was counting forward, but I was only counting "real" months (i.e. calendar months fully included in the given range) as "months", similar to what companies that charge per month do.

                     

                    Of course, the real point is that the expression "years, months and days" is meaningless.

                    • 7. Re: Age (years, months, days) at death
                      RayCologon

                      Michael Horak wrote:

                      Actually, this is an incorrect assumption...

                       

                      Hi Michael,

                       

                      No, sorry, but it's not. Measuring - in any direction - is an exercise that moves from the largest increments to the smaller, and although you expressed your 'counting' in a forward direction by ordering the dates from earliest to latest, you delineated the large increment (month) as being at the end of the period and the smaller increment (a remainder) preceding it. That's counting backwards, regardless of whether you cite the dates forwards.

                       

                      Michael Horak wrote:

                      Of course, the real point is that the expression "years, months and days" is meaningless.

                       

                      As long as there is a common understanding about what it means, then it isn't meaningless. Unless stated otherwise, month as a measure of age is (in my experience) taken to mean calendar month, and remainders expressed in days are (also unless stated otherwise) taken from the end of a period after whole calendar months have all been counted off. It's a widely used convention, and I'd be surprised if you haven't encountered it. ;)

                       

                      The convention thinly disguises the anomaly that arises from months being of differing lengths, and that, perhaps, is your real point. But you may have to argue that one with Julius Caesar.

                       

                      Regards,

                      Ray

                      ------------------------------------------------

                      R J Cologon, Ph.D.

                      FileMaker Certified Developer

                      Author, FileMaker Pro 10 Bible

                      NightWing Enterprises, Melbourne, Australia

                      http://www.nightwingenterprises.com

                      ------------------------------------------------

                      • 8. Re: Age (years, months, days) at death
                        comment

                        Here's a modified example:

                         

                        Start date:April 30, 2011

                        End date: June 5, 2011

                        • From April 30 till May 1: 1 day;
                        • From May 1 till June 1: 1 month;
                        • From June 1 till June 5: 4 days;

                        1 month and 5 days

                         

                         

                        Ray Cologon wrote:


                        Measuring - in any direction - is an exercise that moves from the largest increments to the smaller

                         

                        I am aware of no such rule. Usually it works out to be the most convenient order, but that's all it is: a convenience. If you are on a highway, and all you have is a pair of feet and milestones, you'll work in the same order as I did above.

                         

                         

                        Ray Cologon wrote:

                         

                        It's a widely used convention, and I'd be surprised if you haven't encountered it.

                         

                        LOL, I didn't expect to discuss the meaning of "meaningless". Let me just say that being a convention does not impart a meaning. There is also another "convention" stating that the distance from A to B is equal to the distance from B to A.

                        • 9. Re: Age (years, months, days) at death
                          RayCologon

                          Michael Horak wrote:

                          ...LOL, I didn't expect to discuss the meaning of "meaningless". Let me just say that being a convention does not impart a meaning. There is also another "convention" stating that the distance from A to B is equal to the distance from B to A.

                           

                          Thanks Michael,

                           

                          FWIW, I still think your argument is with Julius Caesar.

                           

                          Cheers,

                          Ray

                          ------------------------------------------------

                          R J Cologon, Ph.D.

                          FileMaker Certified Developer

                          Author, FileMaker Pro 10 Bible

                          NightWing Enterprises, Melbourne, Australia

                          http://www.nightwingenterprises.com

                          ------------------------------------------------

                          • 10. Re: Age (years, months, days) at death
                            comment

                            Ray,

                             

                            I have no argument with anyone. I am merely pointing out that this calculation is meaningless, and as such is always going to produce ridiculous results in some cases.

                             

                            BTW, what does your formula say about person born on January 31, 2000, died on March 3, 2010?

                            Or, for that matter, what does Winfried Huslik's formula say about person born on February 1, 2000, died on March 30, 2010?

                            • 11. Re: Age (years, months, days) at death
                              RayCologon

                              Michael Horak wrote:

                              ...I have no argument with anyone. I am merely pointing out that this calculation is meaningless, and as such is always going to produce ridiculous results in some cases.

                               

                              Hi Michael,

                               

                              I can see where you're coming from. The calendar is ideosyncratic (because months are unequal), so the calculation results will (and should, IMO) reflect that. Whether it means what some may assume it means is a whole other question.

                               

                              Michael Horak wrote:

                              BTW, what does your formula say about person born on January 31, 2000, died on March 3, 2010?

                               

                              Good pickup - thanks! That and a few related edge cases were not giving the intended results. I've now revised the formula and example I posted above with transition offsets that address that and other cases like it. If you see any other combinations that are similarly problematic I'd be interested to hear.

                               

                              All the best,

                              Ray

                              ------------------------------------------------

                              R J Cologon, Ph.D.

                              FileMaker Certified Developer

                              Author, FileMaker Pro 10 Bible

                              NightWing Enterprises, Melbourne, Australia

                              http://www.nightwingenterprises.com

                              ------------------------------------------------

                              • 12. Re: Age (years, months, days) at death

                                Hi Ray 

                                 

                                Unless I have something wrong in my copy/paste of your calculation, it produces no results on this:

                                 

                                DateOfBirth: 2/29/2004

                                DateOfDeath: 2/28/2005

                                 

                                Nice calculation anyway! 

                                • 13. Re: Age (years, months, days) at death
                                  beverly

                                  Must be that funny way of doing dates down under (ha ha!)

                                   

                                  I haven't looked at the samples in this thread, but often when faced with this kind of request, I'll google for a javascript that does what I want.

                                   

                                       1. I can push it into Web Viewer

                                  OR

                                       2. I can "translate" it into FM script/calcs/custom functions.

                                   

                                  usually the javascripts have worked out all the "gotchas" that you need to be aware of (leap day, leap year, other calendar oddities). It may be worth a look see.

                                   

                                  Beverly

                                  • 14. Re: Age (years, months, days) at death
                                    comment

                                    Hi Ray,

                                     

                                    I am afraid our calc-breaking service is closed for today. However, let me point out something that has nothing to do with calculations, only with the "logic" of this convention: Imagine you are at a cemetery, looking at two adjacent stones. The first stone says:

                                     

                                    "Our beloved A, born on March 31, 2011 was taken away from us on May 1, 2011, after living only one month and one day."

                                     

                                    The second stone says:

                                     

                                    "Our beloved B, born on March 30, 2011 was taken away from us on May 1, 2011, after living only one month and one day."

                                     

                                     

                                    Kind of embarrassing, isn't it?

                                    1 2 3 Previous Next