Forum Discussion

Qrew Cadet

6 months ago

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
6 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

Qrew Commander

6 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
Qrew Cadet
6 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!!