Forum Discussion

ReneeHansen1's avatar
ReneeHansen1
Qrew Cadet
11 months ago
Solved

Create a formula duration over 5 years

Hello, I am trying to figure this out How to calculate a duration for each year based on a varying start date? The start date can be at any month of the year (just calculating months, not days) a...
formulas
  • ReneeHansen1's avatar
    ReneeHansen1
    11 months ago

    Thank you! I ended up joining a Quickbase Office Hours with Sam, and we discussed this option:

    1st year:

    var date SD=[Start Date];
    var date ED=LastDayOfYear([Start Date]);
     
    (Year($ED)-Year($SD))/12 // start off with 12 months
     
    +If(Month($ED)>=Month($SD),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),0,0) // but if the day did not catch up to the start date's day number, then subtract 1.

    2nd Year:

    var number secondyear=[Months Needed]-[First Year Duration];

    If($secondyear>12,12,$secondyear)

    and then repeat the pattern for 3, 4, 5 years.  Then My calculation fields are working independently of any variables mashed into one field.  

    *(Posted to help anyone else!)

    Thanks!!

DonLarson's avatar
DonLarson
Qrew Elite
11 months ago

If I understand your question, you are:

  •  looking for the number of months remaining in Year 1 if you need the whole year
  •  then the total number of months from the Start Date to the end of Year 2 if needed
  •  and so forth.

For Year 1 a Formula Numeric Field gives you:

var numeric MonthsLeftYearOne =12 -Month([Start Date])+1;

if( $MonthsLeftYearOne > [Months Needed], $MonthsLeftYearOne, [Months Needed]) 

This tells us that if there are more months left in the year than [Months Needed], then we only will consume the [Months Needed].

 

Each additional formula for Year 2, Year 3 ...   has to evaluate if the [Months Needed] would extend into that Year, if it does not the answer is zero.  If it does extend into that Year however many months up to 12 will be the answer.

For Year 2

var bool YearTwoTrue = If ([Months Needed]-[Year 1] > 12, true, false);

var numeric YearTwoMonths = if ([Months Needed]-[Year 1]>12, 12, [Months Needed]-[Year 1]);

if( $YearTwoTrue = true, $YearTwoMonths, 0)

Then repeat the pattern for your additional years in the field Year 3, Year 4, Year 5.   

This will give you a Numeric Result.   If you intend to use this info to calculate other dates, then you will have to convert it to a real Duration of some type.

 

 

ReneeHansen1's avatar
ReneeHansen1
Qrew Cadet
11 months ago

Thank you! I ended up joining a Quickbase Office Hours with Sam, and we discussed this option:

1st year:

var date SD=[Start Date];
var date ED=LastDayOfYear([Start Date]);
 
(Year($ED)-Year($SD))/12 // start off with 12 months
 
+If(Month($ED)>=Month($SD),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),0,0) // but if the day did not catch up to the start date's day number, then subtract 1.

2nd Year:

var number secondyear=[Months Needed]-[First Year Duration];

If($secondyear>12,12,$secondyear)

and then repeat the pattern for 3, 4, 5 years.  Then My calculation fields are working independently of any variables mashed into one field.  

*(Posted to help anyone else!)

Thanks!!