
1. Re: Need help calculating elapsed time in years, months, and days.
raybaudi May 30, 2011 11:06 AM (in response to Heather)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 Jun 1, 2011 2:01 AM (in response to Heather)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 CASEThis 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 Jun 1, 2011 2:02 AM (in response to Heather)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 CASEthis will result in: 3 months, 6 days

4. Re: Need help calculating elapsed time in years, months, and days.
LaRetta_1 May 31, 2011 6:14 PM (in response to Heather)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 May 31, 2011 6:54 PM (in response to 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 Jun 1, 2011 12:20 AM (in response to Heather)"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 Jun 1, 2011 12:31 AM (in response to 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 Jun 1, 2011 1:14 AM (in response to Heather)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 Jun 1, 2011 1:17 AM (in response to 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 Nov 7, 2011 11:21 PM (in response to Heather)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.