3 Replies Latest reply on Jun 25, 2014 6:39 AM by philmodjunk

    End of Month Expiration

    EricDettrey

      Title

      End of Month Expiration

      Post

           I'm currently working with FileMaker 6 and FileMaker 13. We are rebuilding our database from 6 to 13. 

           I cannot seem to come up with a calculation for an expiration date that will end at the end of the month in which the completed date occurs. If I have a complete date of 6/4/2013, I need the system to automatically set the expiration date to 6/30/2014; the end of the month in which the completed date occurred. 

           I need am looking for help with a calculation for both version 6 and 13.

           Can anyone help me with this?

           Thanks,

        • 1. Re: End of Month Expiration
          philmodjunk

               As far as I know this calculation works in both versions:

               Date ( Month ( CompleteDate ) + 1 ; 0 ; year ( CompleteDate ) )

               For a complete date of 6/4/2014 (assuming mm/dd/yyyy formatting), this evaluates as:

               Date ( 7 ; 0 ; 2014 )

               or

               6/30/2014

               the date for the last day of the month. This will work for any date, including December dates and February dates during leap years.

          • 2. Re: End of Month Expiration
            EricDettrey

                 Thank you very much PhilModJunk. The calculation makes sense to me as you had written it. I tried it in Filemaker 6 and I get the message "Either and operator was omitted, this function cannot be found, or an "(" was not expected here." It's referring to the portion "0; year (". I've tired several different changes but none seem to work. I copied exactly as you had provided. I have not tried it in 13.

            • 3. Re: End of Month Expiration
              philmodjunk

                   That sounds like an error in setting up the calculation. The most common cause of it is either a missing or extra delimitter such as ; or ), both of which are used in this expression.

                   Feel free to post a screen shot of your specify calculation dialog if you can't figure this one out.

                   You may not be able to just copy and paste this as I don't know exactly how to reference the date field in your system. You may need to enter or past the text up to the reference to your date field and then double click to add the exactly correct reference to that field, then add the rest of the expression.

                   I also do not have a copy of FileMaker 6 to test this on, but have used this expression before in much newer versions with no issues. So you should have no trouble using this in the newer version.