Forum Discussion

ReneeHansen1's avatar
ReneeHansen1
Qrew Cadet
17 days 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) and the project will last over the next 5 years. 
Example: IF the Start date is September 2024, there's 4 full months for the first year. And if they're needed for ([Months Needed] is for 12 or 18 or 60 months, then how would I find the duration of the next year, and the next?
Would each year need it's own duration field? Like Year 1 duration, Year 2 duration, etc?

I'll have these fields
[YEAR 1], [YEAR 2], [YEAR 3], [YEAR 4], [YEAR 5]. 
[Start Date]
[Months Needed]

formulas
  • ReneeHansen1's avatar
    ReneeHansen1
    16 days 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!!

2 Replies

Sort By
  • DonLarson's avatar
    DonLarson
    Qrew Commander
    17 days 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
      16 days 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!!