Discussions

 View Only
  • 1.  Calculate number of months between a Start Date and End Date

    Posted 02-18-2021 10:35
    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
    ------------------------------


  • 2.  RE: Calculate number of months between a Start Date and End Date

    Posted 02-18-2021 11:06
    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/
    ------------------------------



  • 3.  RE: Calculate number of months between a Start Date and End Date

    Posted 02-18-2021 11:19
    Fantastic! #1 works like a charm.​

    ------------------------------
    George Bramhall
    ------------------------------



  • 4.  RE: Calculate number of months between a Start Date and End Date

    Posted 10 days ago
    Thanks Blake -- this was a terrific solution!

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