Forum Discussion

ChrisFaye1's avatar
ChrisFaye1
Qrew Cadet
6 years ago

Counting Months based on two date field with rounding

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
Formulas and functions
  • TomMusto's avatar
    TomMusto
    Qrew Cadet
    6 years ago
    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
  • LisaSell1's avatar
    LisaSell1
    Qrew Trainee
    6 years ago
    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