9 Replies Latest reply on Jan 15, 2016 1:55 PM by Mike_1

    How do I calculate age of account?

    Mike_1

      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

       

      Thanks

      Mike

        • 1. Re: How do I calculate age of account?
          jbrown

          Howdy.

          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:

           

          Let ([

          _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...

          • 2. Re: How do I calculate age of account?
            Extensitech

            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.

             

            Chris Cain

            Extensitech

            • 3. Re: How do I calculate age of account?
              jbrown

              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!.

              • 4. Re: How do I calculate age of account?
                DavidJondreau

                Jeremy,

                 

                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?

                 

                Let ([

                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"

                ];

                result

                )

                • 5. Re: How do I calculate age of account?
                  Extensitech

                  Nice.

                   

                  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.

                   

                  Just sayin'

                   

                  Chris Cain

                  Extensitech

                  • 6. Re: How do I calculate age of account?
                    jbrown

                    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

                    • 7. Re: How do I calculate age of account?
                      DavidJondreau

                      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.

                      • 8. Re: How do I calculate age of account?
                        Mike_1

                        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.

                         

                        Mike

                        • 9. Re: How do I calculate age of account?
                          Mike_1

                          Thank you everyone for your help! Now to see if I get it to work!   Thanks!

                           

                          Mike