Discussions

 View Only
  • 1.  Month between 2 dates calculation

    Posted 10-22-2020 16:02
    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
    ------------------------------


  • 2.  RE: Month between 2 dates calculation

    Posted 10-22-2020 16:58
    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
    ------------------------------



  • 3.  RE: Month between 2 dates calculation

    Posted 10-26-2020 11:02
    Mark;  This look good, but provides me the Term in years.  How would you suggest I convert that to months?

    ------------------------------
    Eric Fixler
    ------------------------------



  • 4.  RE: Month between 2 dates calculation

    Posted 10-26-2020 20:49
    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
    ------------------------------



  • 5.  RE: Month between 2 dates calculation

    Posted 10-28-2020 12:17
    (((12*Year([Maturity Date]))+Month([Maturity Date]))-((12*Year([Origination Date]))+Month([Origination Date])))

    This seems to accomplish the correct output also.

    Thanks.

    ------------------------------
    Eric Fixler
    ------------------------------