Calculate respective years based on start date and term

Erez

Hi, I'm looking to dynamically calculate and populate the year in the corresponding columns based on start date and the term length which is up to 5 years, so overall 6 years, including the current year and partial years.

For example, for a July 1, 2025 start date and term of 48 months, I would like to see the years 2025, 2026, 2027, 2028 and 2029 in separate columns. Below is what I want it to look like:

I already calculated the end date based on looking it up in this community, and appreciate all your help!

NickStaffordPM

Hi!

So I would approach it like this… This assumes your Term in Months is always divisable by 12. I could adjust for half years as well if you need.

YEAR 1

=if([Term in Months]@row/12>.99,1+YEAR([Start Date]@row,"")

YEAR 2

=if([Term in Months]@row/12>1.99,1+[Year 1]@row,"")

YEAR 3

=if([Term in Months]@row/12>2.99,1+[Year 2]@row,"")

YEAR 4

=if([Term in Months]@row/12>3.99,1+[Year 3]@row,"")

YEAR 5

=if([Term in Months]@row/12>4.99,1+[Year 4]@row,"")

YEAR 6

=if([Term in Months]@row/12>5.99,1+[Year 5]@row,"")

You could extend this for as many years as you need by following the pattern in the logical statement portion of the IF.

Let me know if this does not work and/or if anyone has any other ideas!

NickStaffordPM

Maybe something like this? Not sure how this will handle it to be honest… Let me know if it doesn't work and I can build something less generic for you.

YEAR 1

=IF([Term in Months]@row>=12, YEAR([Start Date]@row) + 1, YEAR([Start Date]@row) + [Term in Months]@row/12)

YEAR 2

=IF([Term in Months]@row>=24, YEAR([Start Date]@row) + 2, YEAR([Start Date]@row) + MIN(2, [Term in Months]@row/12))

YEAR 3

=IF([Term in Months]@row>=36, YEAR([Start Date]@row) + 3, YEAR([Start Date]@row) + MIN(3, [Term in Months]@row/12))

YEAR 4

=IF([Term in Months]@row>=48, YEAR([Start Date]@row) + 4, YEAR([Start Date]@row) + MIN(4, [Term in Months]@row/12))

YEAR 5

=IF([Term in Months]@row>=60, YEAR([Start Date]@row) + 5, YEAR([Start Date]@row) + MIN(5, [Term in Months]@row/12))

YEAR 6

=IF([Term in Months]@row>=72, YEAR([Start Date]@row) + 6, YEAR([Start Date]@row) + MIN(6, [Term in Months]@row/12))

NickStaffordPM

Hi!

So I would approach it like this… This assumes your Term in Months is always divisable by 12. I could adjust for half years as well if you need.

YEAR 1

=if([Term in Months]@row/12>.99,1+YEAR([Start Date]@row,"")

YEAR 2

=if([Term in Months]@row/12>1.99,1+[Year 1]@row,"")

YEAR 3

=if([Term in Months]@row/12>2.99,1+[Year 2]@row,"")

YEAR 4

=if([Term in Months]@row/12>3.99,1+[Year 3]@row,"")

YEAR 5

=if([Term in Months]@row/12>4.99,1+[Year 4]@row,"")

YEAR 6

=if([Term in Months]@row/12>5.99,1+[Year 5]@row,"")

You could extend this for as many years as you need by following the pattern in the logical statement portion of the IF.

Let me know if this does not work and/or if anyone has any other ideas!

Erez

Thank you! It definitely works for full years and 12+ months. Is there anything that could be done to calculate partial years? For example, 10 months or 30 months?

NickStaffordPM

Maybe something like this? Not sure how this will handle it to be honest… Let me know if it doesn't work and I can build something less generic for you.

YEAR 1

=IF([Term in Months]@row>=12, YEAR([Start Date]@row) + 1, YEAR([Start Date]@row) + [Term in Months]@row/12)

YEAR 2

=IF([Term in Months]@row>=24, YEAR([Start Date]@row) + 2, YEAR([Start Date]@row) + MIN(2, [Term in Months]@row/12))

YEAR 3

=IF([Term in Months]@row>=36, YEAR([Start Date]@row) + 3, YEAR([Start Date]@row) + MIN(3, [Term in Months]@row/12))

YEAR 4

=IF([Term in Months]@row>=48, YEAR([Start Date]@row) + 4, YEAR([Start Date]@row) + MIN(4, [Term in Months]@row/12))

YEAR 5

=IF([Term in Months]@row>=60, YEAR([Start Date]@row) + 5, YEAR([Start Date]@row) + MIN(5, [Term in Months]@row/12))

YEAR 6

=IF([Term in Months]@row>=72, YEAR([Start Date]@row) + 6, YEAR([Start Date]@row) + MIN(6, [Term in Months]@row/12))

Erez

Thank Nick for all your help, it worked!