3 Replies Latest reply on Dec 4, 2014 7:46 AM by philmodjunk

    Calculating months from days



      Calculating months from days


      Hi! I have a question that I imagine will be super easy for the wizards on this forum.

      I have a calculation field that uses a basic calculation to get the number of days in a period:

      DaysLeftInPeriod = PeriodEndDate - PeriodStartDate

      I'd like to deduce the months left in the period using a more precise calculation than the one I'm currently using:

      Case (Not IsEmpty (PeriodEndDate) ; Round (DaysLeftInPeriod / 30 ; 1 ) ; 0 )

      I know that this gives me inconsistent months, since some months have 31 days. Is there a function or calculation that calculates months from days more accurately?


        • 1. Re: Calculating months from days

          A month can be 28, 29, 30 and 31 days in length. How do you plan on defining a "month"? What do you want to do with partial months?

          Do you want to count a month as the interval from day X of Month 1 to day x of month 2? And if PeriodEnd Date is on Month 3, day x + 5, what result do you want to show for the 5 additional days more than 2 months?

          And can the Peroid start and/or Period End dates fall in the 28th through 31st day interval where there might not be a corresponding day in the preceding months of the specified date interval?

          • 2. Re: Calculating months from days

            Great point! Thanks!

            Now that you mention all of that, perhaps my calculation is good enough as it is. I'm rounding to the nearest 1 anyways, so there's no point in trying to get the month numbers down exactly. Now that I really think about it, an approximation is good enough. At least I know that Filemaker is calculating the number of days between two dates accurately. So that number divided by 30, rounded to 1 should suffice.

            Sorry, false alarm! :)

            • 3. Re: Calculating months from days

              For anyone reading this thread in search of an answer. It IS possible to return a result based on the day of the month and that produces a result of "A months, B days..."