### 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?

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