10 Replies Latest reply on Nov 7, 2011 11:21 PM by DavidSmith1348

    Need help calculating elapsed time in years, months, and days.

    Heather

      Title

      Need help calculating elapsed time in years, months, and days.

      Post

      I'm a self taught database user who is largely unfamiliar with mathematical functions.  I want to create a field that will calculate the length of time a student has been in my piano studio.  I have the fields "Joined Studio" and "Length of Study" created but I don't know how to program the calculation.  I want the result to show as years, months, and days.  Can anyone help me?

        • 1. Re: Need help calculating elapsed time in years, months, and days.
          raybaudi

          Hi

          an UNSTORED calculation, result number:

          If ( Joined Studio ; Get ( CurrentDate ) - Joined Studio )

          will give you the total number of days.

          To get years and months, you must make some assumptions, because both of them do not contain always the same number of days.

          • 2. Re: Need help calculating elapsed time in years, months, and days.
            LaRetta_1

            Hi Heather,

            As raybaudi points out, the answer will vary depending upon what you consider a 'month' because the month varies.  However, I think we can get very close (even taking into account assumptions that the definition of a month may vary:

            Case ( JoinedStudio  ≤ Get ( CurrentDate ) ;
            Let ( [
            start = JoinedStudio ;
            next = Date ( Month ( start ) + 1 ; Day ( start ) ; Year ( start ) ) ;
            end = Get ( CurrentDate ) ;
            x = 12 * ( Year ( end ) - Year ( start ) ) + Month ( end ) - Month ( start )- ( Day ( end ) < Day ( start ) ) ;
            y = Div ( x ; 12 ) ;
            m = Mod ( x ; 12 )  ;
            d = Case ( Day ( start ) > Day ( end )  ; Day ( next - Day ( next ) ) +  Day ( end )  - Day ( start )  ; Day ( end ) - Day ( start ) )
            ] ;
            y & " year" & Case ( y > 1 ; "s" )  & ", " &
            m & " month" & Case ( m > 1 ; "s" ) & ", " &
            d & " day" & Case ( d > 1 ; "s" )
            ) // END LET
            ) // END CASE

            This calculation result must be set to DATE TEXT and checked as 'do not store...' in Storage Options.  I changed 'today' to Get ( CurrentDate ) and theDate to Joined Studio. I use 'today' for testing only.  Also, I realized that, since I wanted to display years (even if none) and display months (even if none), then the calc could be shorted accordingly.

            This accounts for leap years because it looks to the total number of days in the month for the start month and then adds the number of days from the end month. 

            This will result in: 0 year, 3 months, 0 day

            • 3. Re: Need help calculating elapsed time in years, months, and days.
              LaRetta_1

              If you wish to drop years if there are none or drop months if there are none, you can use this calculation:

              Case ( JoinedStudio ≤ Get ( CurrentDate )  ;
              Let ( [
              start = JoinedStudio ;
              next = Date ( Month ( start ) + 1 ; Day ( start ) ; Year ( start ) ) ;
              end = Get ( CurrentDate ) ;
              x = 12 * ( Year ( end ) - Year ( start ) ) + Month ( end ) - Month ( start )- ( Day ( end ) < Day ( start ) ) ;
              y = Div ( x ; 12 ) ;
              m = Mod ( x ; 12 )  ;
              d = Case ( Day ( start ) > Day ( end )  ; Day ( next - Day ( next ) ) +  Day ( end )  - Day ( start )  ; Day ( end ) - Day ( start ) )
              ] ;
              Case ( y ; y & " year" & Case ( y > 1 ; "s" )  & ", " ) &
              Case ( m ; m & " month" & Case ( m > 1 ; "s" ) & ", " ) &
              Case ( d ; d & " day" & Case ( d > 1 ; "s" ) )
              ) // END LET
              ) // END CASE

              this will result in:  3 months, 6 days

              • 4. Re: Need help calculating elapsed time in years, months, and days.
                LaRetta_1

                BTW, Raybaudi, about this calculation you presented:

                If ( Joined Studio ; Get ( CurrentDate ) - Joined Studio )

                I realize you are using the If() test so that it doesn't evaluate if the Joined Studio is empty which, one might assume would produce a number such as 734288 if Joined Studio is empty.  However, Get ( CurrentDate ) is not considered a field and, since the only field in this calculation is Joined Studio, it wouldn't evaluate anyway (because it is default checked below as 'do not evaluate if all referenced FIELDS are empty').

                Therefore, you could list it simply as ...  Get ( CurrentDate ) - Joined Studio ... and drop the If() entirely.  :^)

                It is an easy mistake ... I made the same one about six years ago.

                • 5. Re: Need help calculating elapsed time in years, months, and days.
                  Heather

                  Thanks a million for all of this help.  I'm going to try this in the morning.  I'll let you know it goes.  I really appreciate it.  I'm completely new to writing scripts even though I used the old Appleworks Database extensively.

                  • 6. Re: Need help calculating elapsed time in years, months, and days.
                    raybaudi

                    "Therefore, you could list it simply as ...  Get ( CurrentDate ) - Joined Studio ... and drop the If() entirely.  :^)"

                    Correct !
                    ( I had the "Do not evaluate..." unflagged )

                    • 7. Re: Need help calculating elapsed time in years, months, and days.
                      Heather

                      Ok, I just tried pasting in LaRetta's first calculation, setting the result to DATE, and checking the DO NOT STORE storage option.  I got an error message saying that there is no field titled "Today".  I changed that to Get (CurrentDate) to see if that would work.  NOw I get a ? result.  I am definitely in over my head here.  

                      • 8. Re: Need help calculating elapsed time in years, months, and days.
                        LaRetta_1

                        Set the result to text and not date.  And I mentioned that I had changed it from Today to Get ( CurrentDate ).  Try the second calculation as well and compare the results to see which you like best.

                        • 9. Re: Need help calculating elapsed time in years, months, and days.
                          Heather

                          IT WORKED!  IT WORKED!  IT WORKED!  You are a genius.  Bless you!!!!!!

                          • 10. Re: Need help calculating elapsed time in years, months, and days.
                            DavidSmith1348

                            I need help with a formula for finding the years, months and days between two stable dates.  History is the field and  I need to calculate birthdates and death dates to find the number of years, months, and day, and the number of days lived.  I've played around with the above information and with those posted under the support but they deal with  Get Current date.  Could you help me?  I would greatly appreciate being able to add this to my database.  I've noted many of the death certificates are incorrect in listing Yr Mo Da data and I'd be able to compare the two outcomes, rather than doing each record individually as there are over 200,000 records.