Forum Discussion

GeorgeBramhall1's avatar
GeorgeBramhall1
Qrew Member
5 years ago

Calculate number of months between a Start Date and End Date

I am trying to calculate the number of months between a start and end date. Start and End may not be in the same year.

For Example:
Start Date is June 1, 2021 and end date is October 31, 2023. I would like to include the months of both Start and End dates. Answer should be 29 months.

What might my Numeric Formula look like?


------------------------------
George Bramhall
------------------------------
  • You'll need to do this in 3 parts: 
    1. Number of Months Start Date Month to the end of the year
    2. Number of Months from January to the End Date Month
    3. Number of Whole years between the Start Date and End Date

    var date SD=[Start Date];
    var date ED=[End Date];
    
    //Number of Months in Start Year
    var number sm = 13 - Month($sd);
    
    //Number of Months in End Year
    var number em = Month($ed);
    
    //Number of Months between Start and End
    var number wm = (Year($ED)-Year($SD)-1)*12;
    
    //Total
    $sm + $em + $wm​
    ​Alternatively, you can do it this way:
    var date SD=[Start Date];
    var date ED=[End Date];
    
    // start off with 12 times the number of fully completed years
    (Year($ED)-Year($SD)-1)*12 
    
    //add 12 months if the last year was fully completed
    +If(Month($ED)>=Month($SD),12,0)
    
    // add the number of months between the month numbers
    +If((Month($ED)>=Month($SD)),Month($ED)-Month($SD),12+Month($ED)-Month($SD))
    
    // but if the day did not catch up to the start date's day number, then subtract 1
    -If(Day($ED)<Day($SD),1,0) ​


    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quickbase Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
    • GeorgeBramhall1's avatar
      GeorgeBramhall1
      Qrew Member
      Fantastic! #1 works like a charm.​

      ------------------------------
      George Bramhall
      ------------------------------
    • MalcolmMcDonald's avatar
      MalcolmMcDonald
      Qrew Cadet
      Thanks Blake -- this was a terrific solution!

      ------------------------------
      Malcolm McDonald
      ------------------------------