Discussions

 View Only
  • 1.  Counting Months based on two date field with rounding

    Posted 08-09-2019 17:20
    Hi, 

    Need some help with a formula to count the number of months by referencing start and end date fields. I need this to round to the nearest whole number. The formula I'm currently using is below. 

    The issue with this formula is that it doesn't equal the proper number of months when the end date-month doesn't match the beginning date-month... it shows up as 1 less. 

    Ex: 11-01-19 to 10-31-20 = 11 instead of 12 months
    But it functions correctly when counting 11-02-19 to 11-01-20 as 12 Months

    Background: This is regarding lease dates and should round the months to the nearest whole number. Ex: If a few days short of 12 months, should still show 12 months. The rounding split can be the standard halfway mark. 

    Any help with this one would be amazing! 

    Thank You!

    var date SD=[Actual: Lease Start Date];var date ED=[Actual: Lease End Date];

    var number NumMonths =
    (Year($ED)-Year($SD)-1)*12 + If(Month($ED)>=Month($SD),12,0) + If((Month($ED)>=Month($SD)),
    Month($ED)-Month($SD),
    12+Month($ED)-Month($SD));

    $NumMonths


  • 2.  RE: Counting Months based on two date field with rounding

    Posted 08-12-2019 19:58
    Any help on this one?


  • 3.  RE: Counting Months based on two date field with rounding

    Posted 08-13-2019 14:17
    I tried this:
    var date SD=[Actual Start];
    var date ED=[Actual End];
    var number NumMonths =
    Floor(ToWeeks([Actual End]-[Actual Start]) /4.345);

    $NumMonths


    I made a formula on the report & specified 2 decimal places - you may have to adjust the rounding


  • 4.  RE: Counting Months based on two date field with rounding

    Posted 08-13-2019 14:52
    I believe the following formula works without having to deal with rounding or using approximations to convert into months:

    var Number yrDiff = Year([Date End]) - Year([Date Start]);
    var Number startMo = Month([Date Start]);
    var Number endMo = If(ToNumber(Day([Date End]))/ToNumber(Day(LastDayOfMonth([Date End])))>= 0.5, Month([Date End])+1, Month([Date End]));
    $yrDiff*12 + $endMo - $startMo

    I spot checked a few dates and it appeared to calculate correctly based on the guidelines you provided. This assumes that you don't want the same rounding on the start date as you do on the end date. If you do want that same rounding, you would need to copy/paste the endMo formula to startMo and replace the field references to reference your start date. Hope this helps.

    -Tom