1 2 Previous Next 19 Replies Latest reply on Dec 13, 2016 12:58 PM by fmpdude

    Calculating Years

    apatlan

      I have a date field and would like to put a formula/calcuation in another field to calculate and add years.

       

      Example: 

      Hire date field: 1-19-2000

      Longevity field: 1-19-2020 (add years to hire date)

       

      Can this be done?

        • 1. Re: Calculating Years
          David Moyer

          Hi,

          a simple way is

          Set $newDate = Date ( Month ( $oldDate ); Day ( $oldDate ); Year ( $oldDate ) + 20 )

          • 2. Re: Calculating Years
            David Moyer

            If you need a calculated date field, you can use this

            Let(

              hire = TO::DateOfHire;

              Date(

                Month(hire);

                Day(hire);

                Year(hire) + 20

              )

            )

            • 3. Re: Calculating Years
              karina

              Hi,

               

              Another possibility is:

              Month (Hire date) & "-"  & Day ( Hire date )  & "-" & (Year ( Hire date)+20)

               

              greetings,

              Karina

              • 4. Re: Calculating Years
                erolst

                Try

                 

                Let ( [

                  h = dateHire ;

                  yearsToAdd = 20 // as per your example

                  ] ;

                  Date (

                    Month ( h ) ;

                    Day ( h ) ;

                    Year ( h ) + yearsToAdd

                  )

                )

                • 5. Re: Calculating Years
                  fmpdude

                  Yep, as shown, yearsToAdd should come in as a parameter never be hard-coded.

                  • 6. Re: Calculating Years
                    apatlan

                    I think I am doing something wrong.  I can’t make it work.  Can you tell where I need to add the calculation?

                    • 7. Re: Calculating Years
                      David Moyer

                      Here's what it looks like as a calculated field ...

                      My hire date field is just called Date1.

                      • 8. Re: Calculating Years
                        David Moyer

                        yearsToAdd should come in as a parameter never be hard-coded

                         

                        I totally agree with fmpdude.  However, depending on my mood, I tend towards the side of making it simple to the reader.  I know I assume too much; but I hope that folks adapt to their own needs - like not hard-coding "constants"; adding comments; using meaningful field and variable names, optimizing logic, etc.

                        Sometimes I try to teach to fish; other times I pert-near cook it and plate it.

                        • 9. Re: Calculating Years
                          fmpdude

                          I totally know from reading your posts you'd never hard-code a constant and that you were indeed just simplifying the example.

                           

                          Have you ever tried herding cats?

                          • 10. Re: Calculating Years
                            fmpdude

                            Super Simple easy to do. Since there are usually multiple ways to do something, it can get confusing.

                             

                            8:56 AM Dec 13, 2016--> Updated text/graphics below to correct my silly goof of adding 20 days instead of 20 Years (appreciation to @David Moyer for noticing and letting me know).

                             

                             

                             

                            --------------

                             

                            I just created a date field called HIRE_DATE, Type = Date

                             

                            Now, for the LONGEVITY_DATE calculation, I did this two ways:

                             

                            Way 1: No Custom Function Needed

                             

                            For the LONGEVITY_DATE field I had the simple expression:

                            Date( Month(Untitled::HIRE_DATE) ; Day(Untitled::HIRE_DATE) ; (Year(Untitled::HIRE_DATE) + 20))

                             

                            (so, in FMP, create a calculated filed for LONGEVITY_DATE and add that expression ---> Date( Month(Untitled::HIRE_DATE) ; Day(Untitled::HIRE_DATE) ; (Year(Untitled::HIRE_DATE) + 20))).

                             

                            Note: in a real application, you would not hard code the 20. Rather, you'd have a table of constants or read that value from a file (or pass it in).

                             

                            ---------------------------------------------------

                             

                            Way 2: Using a Custom Function

                            If you have FMP Advanced, you can use a Custom Function (really a custom calculation, but whatever):

                             

                            In FileMaker Advanced, click File...Manage....Custom Functions and create a new one like this:

                            Now, in the definition for LONGEVITY_DATE, we just reference the custom calculation instead of typing in the formula to add 20 years to the hire date like we did in example 1 above.

                             

                            Here's what your field definition would look like:

                            Here's what it looks like when you edit the calculation in for the longevity field:

                             

                            Note: Here we are passing the offset (20, in this case):

                            Either using Method 1 or Method 2, the result is the same:

                             

                            HOPE THIS HELPS.

                            • 11. Re: Calculating Years
                              apatlan

                              I did the above calculation and I get an error: "the specified table cannot be found"

                              • 12. Re: Calculating Years
                                David Moyer

                                sorry - the image is too small for my aging eyes.

                                • 13. Re: Calculating Years
                                  apatlan

                                  ( Month(Untitled::Date of Hire) ; Day(Untitled::Date of Hire) ; (Year(Untitled::Date of Hire) + 20))

                                  • 14. Re: Calculating Years
                                    philmodjunk

                                    Had to zoom the browser and then could barely make out very blurry text.

                                     

                                    I think that you've left out the function name "Date" to the left of the first left parenthesis.

                                    1 2 Previous Next