How can I calculate the age of an account by using an established date or start date on the account. I would like to track the age of the acount by YEARS then DAYS
Here's my calc for it. There may be custom functions around (if you have FileMaker Pro Advanced), but I used this in my data viewer:
_Calc = GetAsDate(EMP__Employees::zz_recCreateTimeStamp); //Date this account was created
_Today = Get (CurrentDate);
_Result = (_Today - _Calc) / 365; // Dates can be subtracted. Gives me # of days between. Turn into years.
_Years = Round (_Result; 0 ); // Grab the years (whole number)
_Days = Round (Mod (_Result; 1) *365 ; 0 ) - 1 //Turn decimal into days of a year
_Years & " years, " & _Days & " days"
In this case, the account was created on 1/13/2009. So today, this account is 7 years and 2 days old.
There's probably quicker ways, but this is what I whipped up this early morning.
I wrote comments on each line so you can see how this was done...
You might find this useful, or the link within it to several similar threads:
Age from dob
In the calc above, it appears that leap years aren't accounted for. Also, I believe you should use int instead of mod in _Days, so it doesn't round up. I could be wrong, though.
If you used the calc I provided in that post, you could get the age, and then subtract beginDateInEndYear from the current date to get the number of days.
Ah. You're right about my function. I neglected the rounding up of years.
(this calc put me at 41 years old and 302 days. I'm only 40. !!!!)
So I'd change the years line to INT(_Result).
That's better. I'm still thinking how it would deal with leap years.. . .
I'm now running it through lots of dates to check it out.
Thanks for the catch!.
You'd likely better off not using 365 in your calcs...Then you'll have to recreate the rules of leap years. How about this?
test.date = GetAsDate ( EMP__Employees::zz_recCreateTimeStamp ) ;
today = Get (CurrentDate) ;
y.today = year ( today ) ;
y.test = year ( test.date ) ;
m.test = month ( test.date ) ;
d.test = day ( test.date ) ;
test.last.year = date ( m.test ; d.test ; y.today - 1 ) ;
test.this.year = date ( m.test ; d.test ; y.today ) ;
ann.upcoming = test.this.year > today ;
days = If ( ann.upcoming ; today - test.last.year ; today - test.this.year ) ;
years = ( y.today - y.test ) - ( ann.upcoming ) ;
result = years & " years, " & days & " days"
As I pointed out in the linked post, though, this discussion and ones like it seem to pop up at the rate of about once a week, and then we each come up with our favorite way (out of a nearly infinite set of ways) of doing it. Wouldn't it be nice if we just had a thread we could point to when this comes up again (and again, and again, and again?).
Not saying my preferred way is the best or the only, but I for one would like to be able to just say "look here", or better yet have the OP find their answer in the forum history without asking the question anew. It'd save the OP time, too.
I read that post and agree. It is always coming up. I looked for a CF, but couldn't really find one. Obviously I don't have the best answer
Personally, I prefer to ask a question than search through archives, so I'm happy to answer the same ones over and over again. I don't like answering: "Search the archives" for a few reasons. Sometimes, there are slight differences in a request. Like, for example, wanting Years and Days. I haven't seen that one. (I've seen, Years, Months, and Days or just Years. But not Years and Days.), so it's not really helpful. Also, the result in a previous post may be wrong or difficult for one particular individual to understand. I also like discussion threads because we can ask for clarifications, etc, get different angles, hear new techniques. And I like coming up with calculations. So, it works for me.
When I first entered the forum I searched the data base for several phrases and nothing of the sort came up. Maybe I wasnt wording it correctly. I used "Calculate the age of an account", "Calculate age using date", "age of account using time stamp". "Find age of account" Nothing was found.
Thank you everyone for your help! Now to see if I get it to work! Thanks!
Retrieving data ...