7 Replies Latest reply on Feb 2, 2012 9:50 PM by philmodjunk

    Age Calculation in two fields

    JoseHowardBustos

      Title

      Age Calculation in two fields

      Post

      Hello 

      I'm a physician trying to develop a database for a patient follow-up. I'm having a hard time trying to figure out the best way to calculate ages. Does anyone know how to write the 2 formulas for this?

      I have 4 fields: 1. "First visit date" (this is entered manually) 2. "Date of Birth" 3. "current age" 4. "Age of first visit"

      I want the "Current age (in years)" field to show the patient age based on the current date (system date) and that updates automatically.

      and the other field: "Age of first visit" to show the age (in years) of the patient based on the field "First visit date"

      Any help will be deeply appreciated.

        • 1. Re: Age Calculation in two fields
          philmodjunk

          The key to getting the age to update automatically is to use a field of type calculation and select "do not store..." after clicking the storage options button.

          The calculation you'd use for Current Age:

          Let ( D = Get ( CurrentDate ) ; Year ( D ) - Year ( Date of Birth ) - ( Date ( Month ( D ) ; Day ( D ) ; Year ( Date of Birth ) ) > D ) )

          For age of first visit:

          Let ( D = First visit date ; Year ( D ) - Year ( Date of Birth ) - ( Date ( Month ( D ) ; Day ( D ) ; Year ( Date of Birth ) ) > D ) )

          • 2. Re: Age Calculation in two fields
            JoseHowardBustos

            Hello PhilModJunk

            Thank you for helping me out. Well, It sure does calculate the the age in both fields. The only thing is that when I first enter the first visit date it displays in the "age of first visit" 2011. Also the age result is one year ahead.

            If i first enter the "Date of birth" (November 7 1980) without filling the "first visit date" in the "Age of first visit I get -1981 and "Current Age" 32

            When I fill both "First visit date" and "Date of birth" I get as a result 32 which is one year ahead of the real age.

            Is there any way to fix the age and also not to show anything in the "age of first visit" field until both fields are filled?

            • 3. Re: Age Calculation in two fields
              philmodjunk

              The negative can be kept from appearing if you put the calculation for the age at first visit inside an if function that checks for a value:

              Let ( D = First visit date ; If ( D ; Year ( D ) - Year ( Date of Birth ) - ( Date ( Month ( D ) ; Day ( D ) ; Year ( Date of Birth ) ) > D ) ) )

              I don't think your date of birth field is a field of type date like it needs to be or you'd get a much larger negative number than -1981. Make sure that date of birth is of type date.

              • 4. Re: Age Calculation in two fields
                JoseHowardBustos

                How do I substract in both formulas a year to the final result?. I ask you this because, let's say, if someone was born on november, 7 1980 the current age is 31 but the formula is calculating the age as 32 because as of November, 7 2011 you're starting to live your 32nd year but in reality you are 31 and something.

                Thanks a lot!

                • 5. Re: Age Calculation in two fields
                  philmodjunk

                  Apologies. It took me awhile to spot the major oops in the original calculation. I've got today's date and the date of birth data reversed in the last term of the expression--which is the part that subtracts one when the current year's birtday is still in the future:

                  Let ( D = Get ( CurrentDate ) ; Year ( D ) - Year ( Date of Birth ) - ( Date ( Month ( Date of Birth ) ; Day ( Date of Birth ) ; Year ( D ) ) > D ) )

                  • 6. Re: Age Calculation in two fields
                    JoseHowardBustos

                    I almost got it all right! 

                    I figured to modify my "Age of first visit" calculation and worked

                    Let ( D = First visit date ; Year ( D ) - Year ( Date of Birth ) - ( Date ( Month (  Date of Birth ) ; Day (  Date of Birth ) ; Year ( D ) ) > D ) )

                    Now the issue is that if when I fill my "First visit date" with the current date, I get a 2011 in the "Age at first visit" Also, if i only fill the "Date of birth" field with a November, 7 1980 date i get a -1981. I think this is because it is still evaluating the calculation even if one of the fields is empty. This is strange since I checked the "Do not evaluate if all referenced fields are empty" option. It's pretty annoying. Any suggestions? Thanks a lot Phil

                    • 7. Re: Age Calculation in two fields
                      philmodjunk

                      The do not evaluate option only applies if all fields referenced in the calculation are empty. If one field is not empty, other empty number or date fields will evaluate as though they store a zero.

                      I posted an example of using an If function earlier that prevented the calculation from returning a value if the DOB field is empty. Here's an example of an expression that only computes an age if both date of birth and date of first visit contain data:

                      Let ( D = First visit date ; If ( D and Date of Birth ; Year ( D ) - Year ( Date of Birth ) - ( Date ( Month (  Date of Birth ) ; Day (  Date of Birth ) ; Year ( D ) ) > D ) ) )