Calculate the EndDate across Years

System

This discussion was created from comments split from: Date formulas.

Jon Ringler

I want to do something very similar for months versus weeks and see the duration field does not allow months. Can you help?

I have a product sold based on subscription. Subscriptions always have an AcceptanceDateDate and a Term(months) user inputs. I want to auto-calculate the StartDate and EndDate of the subscription. Term is always input as a variable number of months.

The Start Date must be the first day of the next month following acceptance. An acceptance date of 15 Feb 2021 needs to auto-calculate a StartDate of 1 Mar 2021. An acceptance date of 2 Dec 2021, needs to auto-calculate a StartDate of 1 Jan 2022, and so on. This part, I have figured out elegantly, but I'm struggling with the EndDate.

The EndDate must be one day less than the StartDate plus the term. In other words, the EndDate will always end on the last day of the preceding month. Using the above examples, an 18 month term would yield EndDates as follows:

StartDate = 1 Mar 2021 => EndDate = 31 Aug 2022

StartDate = 1 Jan 2022 => EndDate = 30 Jun 2023

To calculate the EndDate, I currently have the following:

DATE(YEAR(AcceptanceDate@row), MONTH(AcceptanceDate@row) + [SubscriptionTerm(months)]@row, DAY(AcceptanceDate@row) - 1)

This works great except when I roll into the next year and return #INVALID VALUE.

Any help is greatly appreciated, the only other solution is 1) Force the EndDate to be user input, or 2) create a very ugly imbedded IF(OR statement.

Toufong Vang

Give the following a try...

First, the start date has to be precise. Use the formula below to calculate the Start Date.

=IFERROR(IF(MONTH(AcceptanceDate@row) < 12, DATE(YEAR(AcceptanceDate@row), MONTH(AcceptanceDate@row) + 1, 1) - 1, DATE(YEAR(AcceptanceDate@row), 12, 31)) + 1, "")

For easier reading...

1  IFERROR( IF(MONTH(AcceptanceDate@row) < 12
2		, DATE(YEAR(AcceptanceDate@row), MONTH(AcceptanceDate@row) + 1, 1) - 1
3		, DATE(YEAR(AcceptanceDate@row), 12, 31) ) + 1
4	, "" )

Then use the following formula to calculate the End Date.

=IFERROR(DATE((YEAR(StartDate@row) + (INT([Term in Months]@row / 12))), IF((MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) > 12, (MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) - 12, (MONTH(StartDate@row) + MOD([Term in Months]@row, 12))), 1) - 1, "")

Formatted for easier reading.

1   IFERROR( DATE( (YEAR(StartDate@row) + (INT([Term in Months]@row / 12)))
2		    ,IF((MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) > 12
3		       ,(MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) - 12
4		       ,(MONTH(StartDate@row) + MOD([Term in Months]@row, 12)))
5	            ,1) - 1
6	   , "")

johntfg

@Toufong Vang

I am using this formula you posted earlier:

=IFERROR(DATE((YEAR(StartDate@row) + (INT([Term in Months]@row / 12))), IF((MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) > 12, (MONTH(StartDate@row) + MOD([Term in Months]@row, 12)) - 12, (MONTH(StartDate@row) + MOD([Term in Months]@row, 12))), 1) - 1, "")

But am trying to modify slightly. I am trying to calculate the end date of a project based on the start date and number of months that I input in my sheet. So for example, my start date column will read 5/15/23, my duration column will show a number of 6 for the amount of months. I want to then have the formula in the finish date column calculate based on these two data points. Is there a way to easily modify your formula above to calculate this specific date?