2 Replies Latest reply on Mar 9, 2011 1:35 PM by jetasent

    Age calculation from DOB field

    jetasent

      Title

      Age calculation from DOB field

      Post

      I am a relatively novice FMP user, so please bear with me if this is a simple calculation. 

      I have two fields in my database: one with the DOB and one that calculates age from the DOB. However, sometimes the DOB is unknown and the field is entered as 1/1/1900.  I need the calculated age to return as "Not provided" when that happens while still calculating an age if another date is entered.  Here is the current age calculation:

      Year(Get(CurrentDate)) - Year(DOB) - If(Get(CurrentDate)< Date(Month(DOB); Day(DOB); Year(Get(CurrentDate))); 1; 0)

      What do I need to add to it to get "Not Provided" to come up if the DOB field equals 1/1/1900?

        • 1. Re: Age calculation from DOB field
          philmodjunk

          Let ( TODAY = get ( CurrentDate ) ;
                  Case ( DOB = Date ( 1 ; 1 ; 1900 ) ; "Not Provided" ;
                            Year ( TODAY ) - Year ( DOB ) - ( TODAY < Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( TODAY ) ) )
                          ) // case
                ) // let

          The return type will now need to be text and this could affect how you can use this field. You may want to use a separate calculation field to return "Not Provided" or an empty string so that this calculation can still function as a date for purposes of finds and sorts.

          Edit note: added missing parenthesis in red to correct typo.

          • 2. Re: Age calculation from DOB field
            jetasent

            I made one adjustment to the calculation because I got an error that a ")" was missing. Here is the updated calculation:

            Let ( TODAY = get ( CurrentDate ) ;
                    Case ( DOB = Date ( 1 ; 1 ; 1900 ) ; "Not Provided" ;
                              Year ( TODAY ) - Year ( DOB ) - ( TODAY < Date ( Month ( DOB ) ; Day ( DOB ) ; Year ( TODAY ) ) )
                            ) // case
                  ) // let