Forum Discussion

EricFixler1's avatar
EricFixler1
Qrew Trainee
5 years ago

Month between 2 dates calculation

I am looking  for some help on a formula.

I have a loan with an origination date and a maturity date - they are both in Date Field format
I am looking to extract the number of whole months between the two dates.

Example
Origination Date: 1/1/2009
Term In months: ????
Maturity Date: 1/1/2019

We are looking for 120 months in the Term Field

Thank you

E

------------------------------
Eric
------------------------------
Formulas and functions
Tables and fields
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Try this, 

    var date Origination = [Origination Date];
    var date Maturity = [Maturity Date];

    Year($Maturity) - Year($Origination)
    -
    If(
    Month($Maturity) < Month($Origination)
    or
    (Month($Maturity) = Month($Origination) and Day($Maturity) < Day($Origination)),1,0)

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • EricFixler1's avatar
      EricFixler1
      Qrew Trainee
      5 years ago
      Mark;  This look good, but provides me the Term in years.  How would you suggest I convert that to months?

      ------------------------------
      Eric Fixler
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        OK, right, you wanted month and I gave you years.

        I did not re-test this formula, but see if it works

        'var date SD=[start date];
        var date ED=[end date];

        (Year($ED)-Year($SD)-1)*12 // start off with 12 times the number of fully completed years

        +If(Month($ED)>=Month($SD),12,0) //add 12 months if the last year was fully completed

        + If((Month($ED)>=Month($SD)),Month($ED)-Month($SD),12+Month($ED)-Month($SD)) // add the number of months between the month numbers.

        -If(Day($ED)<Day($SD),1,0) // but if the day did not catch up to the start date's day number, then subtract 1.




        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------