6 Replies Latest reply on Jan 12, 2016 5:02 AM by tkemmere

    Understanding calculation to distract "1 month" from a date

    tkemmere

      Dear all,

       

      Is someone available to help me understand the (10 year old) calculation presented on

      subtract exactly one month from a date? - Calculation Engine (Define Fields) - FMForums.com

      ? (I don't have an account there and I prefer to ask here).

       

      It says:

      1| Let([

      2| dayWant=Day(dateField);

      3| dayEnd=Day(dateField-dayWant)];

      4| Date(Month(dateField)-1;Day(Min(dayEnd;dayWant));Year(dateField)))

       

      The purpose is to go back one month in time, in this way:

      • 31st of Jan 2016 > 31 Dec 2015,
      • 31 Dec 2015 > 30 Nov 2015,
      • 31 Mar 2016 > 29 Feb 2016,
      • 31 Mar 2015 > 28 Feb 2015.

       

      The calulation works. But I would like to adapt it, so I would need to understand it

       

      Say we take 31 Mar 2016 as an example.

       

      Line 2: dayWant=Day(dateField);

      So what happens: dayWant = the day of "31-Mar-2016" = 31

       

      Line 3: dayEnd=Day(dateField-dayWant)];

      Sowhat happens: dayEnd = the day of "31-Mar-2016" - 31 = 0

       

      Line 4: Date(Month(dateField)-1;Day(Min(dayEnd;dayWant));Year(dateField)))

      Sowhat happens: Put date together with:

      Month: Mar minus 1 = Feb

      Day: lowest of the two options 0 or 31 = 0

      Year: 2016

       

      Somewhere I'm going wrong. And it must be in line 3, but I can't figure it out. Any help is appreciated.

      Please be aware that we're from places where we note dates differently. I thought all this is best readable for everyone using 3 letters for months.

       

      Thanks, Regards, Thomas.

        • 1. Re: Understanding calculation to distract "1 month" from a date
          tkemmere

          Complete description of the calculation is:

          This calculation will jump one month back but stay on the matching day. Exceptions: When the prior month's matching DAY does not exist. If DAY doesn't exist, this calc will produce LAST day of the prior month (and accounts for leap year/cross year. Calc conforms to FM's internal dates and responds properly).

          • 2. Re: Understanding calculation to distract "1 month" from a date
            Mike_Mitchell

            Your basic tool for this is the Date function. It looks like this:

             

            Date ( month ; day ; year )

             

            So, to subtract one month from a given date, you can use this:

             

            Date ( Month ( startDate ) - 1 ; Day ( startDate ) ; Year ( startDate ))

             

            FileMaker will automatically account for preceding years, months, etc. So if I put in 1/11/2016, then the calculation will give 12/11/2015.

             

            Now, your requirement is a little different. Why? Because of the requirement that nonexistent dates process out as the last day of the preceding month. The basic calculation would take 2/28/2016 and return 1/28/2016 - that's fine. But if I put in 3/31/2016, it'll return 3/3/2016. Not what you want. So instead, you would do this:

             

            Let ( [

            baseDate = Date ( Month ( startDate ) - 1 ; Day ( startDate ) ; Year ( startDate )) ;

            prevMonthEnd = Date ( Month ( startDate ) ; 0 ; Year ( startDate ))

            ] ;

             

            Case ( Month ( baseDate ) < Month ( startDate ) ; baseDate ; prevMonthEnd )

             

            )

             

            HTH

             

            Mike

            • 3. Re: Understanding calculation to distract "1 month" from a date
              beverly

              FileMaker's date calculations are able to jump months and years correctly (when using additions & subtractions):

               

              Jan 31 (+ 1 month = Mar ..., depending on leap year/day)

                   Date ( Month(myDate) + 1 ; Day(myDate) ; Year(myDate) )

               

              Dec 12 (+ 30 days = Jan 11, the next year)

                   GetAsDate("12/12/2015") + 30

                   or

                   myDate + 30

               

              Jan 11 (- 30 days = Dec 12, the previous year)

                   GetAsDate("1/11/2015") - 30

                   or

                   Date ( Month(myDate) ; Day(myDate) - 30 ; Year(myDate) )

               

              and a really COOL feature? mm/0/yyyy = the LAST day of the PREVIOUS month (including back a year if needed)! the "0" for the day value is what says, go back a day from the first day of the listed month/year.

                   Date ( Month(myDate) ; 0 ; Year(myDate) )

                   or

                   GetAsDate("1/0/2016") = 12/31/2015

               

              Min() & Max() works on dates, as well so testing the minimum day for any given day will yield the LAST day if it's less than a calculated date that might bump it to the next month.

               

              HTH,

              beverly

              • 4. Re: Understanding calculation to distract "1 month" from a date
                tkemmere

                Dear Mike and Beverly,

                 

                Great!

                 

                I marked this as the correct answer because both your replies answer my question equally well.

                 

                Thanks for putting this in words. I'm going to test it all, so I get the hang of it!

                • Mike, what you put in the Let statement, with the Case, makes it more readable. Nice.
                • Beverly, I'll definately try that 0-feature as well.

                 

                Thanks, regards, Thomas.

                • 5. Re: Understanding calculation to distract "1 month" from a date
                  beverly

                  The is a similar thread on this forum:

                   

                  A month from today?

                   

                  HTH,

                  Beverly

                  • 6. Re: Understanding calculation to distract "1 month" from a date
                    tkemmere

                    I managed! I needed it for contract management.

                    Took me 2,5 days alltoghether but I now have a script that caculates...

                    • Phase description (Contract running;1st extension;2nd extension ...10th extension)
                    • Phase start date
                    • Phase end date
                    • Last notice date
                    • Notice two months before notice

                    Based on...

                    • Start date
                    • Duration (Determined;indertermined)
                    • Duration in months
                    • Extension (Determined;indertermined;none)
                    • Extension duration in months
                    • Notice (Yes;no)
                    • Noticeperiod in months

                    And all to the perfect date! Leapyears, months of 30 and 31, all working. Great!