8 Replies Latest reply on Nov 17, 2016 6:56 AM by lee423

    Expiry Date Report

    lee423

      Hi All

       

      Can anyone help me with a report I have.

       

      The report is showing how many months are left before a qualification will expire. I got the calculation online so I don't really understand it.

      If an expiry date is over 12 months it is showing a zero. I would like to show a true value in months.

      Below is the code and report.

       

        • 1. Re: Expiry Date Report
          TSPigeon

          lee423:

           

          Thank you for your post!

           

          I am going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive more views and potentially more feedback on this topic!

           

          TSPigeon

          FileMaker, Inc.

          • 2. Re: Expiry Date Report
            lee423

            Thank you very much

            • 3. Re: Expiry Date Report
              erolst

              Could you please post the calculation as text, i.e. copy it from the calculation dialog and paste it into a new post?

              • 4. Re: Expiry Date Report
                David Moyer

                Hi,

                I think this will calculate Months to expiration (I tested it with expiry of 11/30/16, 12/31/16 and 5/31/18) ...

                Let(

                  [

                     spreadYear = Year(expiry_date) - Year(Get(currentDate));

                     spreadMonth = Month(expiry_date) - Month(Get(currentDate))

                  ];

                  If(expiry_date < Get(CurrentDate);

                    0;

                    12 * spreadYear + spreadMonth

                  )

                )

                • 5. Re: Expiry Date Report
                  David Moyer

                  Hi again,

                  if that works for you, here's the same thing in a more efficient form:

                  Let(

                    [

                      exp = expiry_date;

                      today = Get(CurrentDate)

                    ];

                    If(exp < today;

                      0;

                      12 * (Year(exp) - Year(today)) + Month(exp) - Month(today)

                    )

                  )

                  • 6. Re: Expiry Date Report
                    lee423

                    Here you Erolst.

                     

                    If( expiry_date  ≥  Date(Month(Current Date);Day(Current Date);Year(expiry_date)) ;

                     

                     

                    If( expiry_date  ≥  Date(Month(expiry_date);Day(Current Date);Year(expiry_date)) ;

                     

                     

                    Month(expiry_date)-Month(Current Date);

                     

                     

                    If( ( Month(expiry_date)-Month(Current Date) )-1 < 0; 12+( Month(expiry_date)-Month(Current Date) )-1; ( Month(expiry_date)-Month(Current Date) )-1 )

                     

                     

                    )

                     

                     

                    ;

                     

                     

                    If( expiry_date  ≥  Date(Month(expiry_date);Day(Current Date);Year(expiry_date)) ;

                     

                     

                    12-(Month(Current Date)-Month(expiry_date));

                     

                     

                    12-(Month(Current Date)-Month(expiry_date))-1

                     

                     

                    )

                     

                     

                    )

                    • 7. Re: Expiry Date Report
                      erolst

                      Hi Lee -

                       

                      I think that's not necessary anymore.

                       

                      I suggest you use David's calculation, which is brief and to the point.

                      • 8. Re: Expiry Date Report
                        lee423

                        Hello David

                         

                        Thank you so very much for your help, I am always astounded how much people give on this community.

                        I really do learn a lot from you all and am always extremely greatfull full.

                         

                        Best Regards

                        Lee