Date formula to return end of following year

Karen Carlson

I am trying to calculate a new date which returns the end of the year following one year from a date. So for instance, the date entered is 3/4/2022, I need to return 12/31/2023.

I have tried the IFERROR formulas but keep getting an error.

Thank you!

Jeff Reisman

@Karen Carlson

My go-to method for this would be to use the DATE function to create the end of next year date value. The DATE function syntax is DATE(year, month, day). You can use formulas or just number values within the DATE function to find your values for year, month, and day.

Let's say you have Date column and EndOfNextYearDate column. In your EndOfNextYearDate column, use the following:

=DATE(YEAR(Date@row) + 1, 12, 31)

The YEAR function extracts the year from a date value. We add 1 to that year to get next year, and then give it month 12 and day 31. For any date in 2022 in the Date column, the calculated end of next year date is 12/31/23.

From my test sheet:

Jeff Reisman

@Karen Carlson

My go-to method for this would be to use the DATE function to create the end of next year date value. The DATE function syntax is DATE(year, month, day). You can use formulas or just number values within the DATE function to find your values for year, month, and day.

Let's say you have Date column and EndOfNextYearDate column. In your EndOfNextYearDate column, use the following:

=DATE(YEAR(Date@row) + 1, 12, 31)

The YEAR function extracts the year from a date value. We add 1 to that year to get next year, and then give it month 12 and day 31. For any date in 2022 in the Date column, the calculated end of next year date is 12/31/23.

From my test sheet:

Karen Carlson

Spectacular! Thank you. Thanks for such a quick reply.

Angelique H.

@Jeff Reisman - Hi Jeff, I came across this discussion and have a question regarding the DATE function. Is there a way to return a specific date in the next year (2023) based on a specific date? The created formula works within the same year, but when the starting date is within Q4, #INVALID VALUE returns.

=DATE(YEAR([Target Completion]$28), MONTH([Target Completion]$28) + 2, 1)

Target Completion value = 11/4/22

The goal is to return the due date for rent payments, first of the month for Dec, Jan 2023, and Feb 2023

Thanks! - Angelique

Jeff Reisman

@Angelique H.

To do this requires using some nested IF statements. First, determine the logic involved for finding the next three months of rent due dates for Target Completion dates falling in Q4:

If the Target Completion month is October, Month 1 would be the current year, 11, 1; Month 2 would be current year, 12, 1; Month 2 would be current year + 1, 1, 1.

If the Target Completion month is November, Month 1 would be current year, 12, 1; Month 2 would be current year + 1, 1, 1; Month 3 would be current year + 1, 2, 1.

If the Target Completion month is December, Month 1 would be current year + 1, 1, 1; Month 2 would be current year + 1, 2, 1; Month 3 would be current year + 1, 3, 1.

So how do you tie that all in to the rest of your formulas?

Month 1 Due Date:

=IF(MONTH([Target Completion]$28) < 12, DATE(YEAR([Target Completion]$28), MONTH([Target Completion]$28) + 1, 1), IF(MONTH([Target Completion]$28) = 12, DATE(YEAR([Target Completion]$28) + 1, 1, 1), ""))

In English: If the month is less than 12, calculate a date for the first day of next month; if the month is December, calculate a date with a year value being the current year + 1, Month = 1, Day = 1; otherwise, leave the cell blank.

Month 2 Due Date:

=IF(MONTH([Target Completion]$28) < 11, DATE(YEAR([Target Completion]$28), MONTH([Target Completion]$28) + 1, 1), IF(MONTH([Target Completion]$28) = 11, DATE(YEAR([Target Completion]$28) + 1, 1, 1), IF(MONTH([Target Completion]$28) = 12, DATE(YEAR([Target Completion]$28) + 1, 2, 1), "")))

Month 3 Due Date:

=IF(MONTH([Target Completion]$28) < 10, DATE(YEAR([Target Completion]$28), MONTH([Target Completion]$28) + 1, 1), IF(MONTH([Target Completion]$28) = 10, DATE(YEAR([Target Completion]$28) + 1, 1, 1), IF(MONTH([Target Completion]$28) = 11, DATE(YEAR([Target Completion]$28) + 1, 2, 1), IF(MONTH([Target Completion]$28) = 12, DATE(YEAR([Target Completion]$28) + 1, 3, 1), ""))))

• As always, make sure your color-coded parentheses match up. Your very first open parentheses should be the same color as the very last close parentheses. For nested IFs, the general rule is that there will be a close parentheses for every "IF" in the formula; In the Month 3 formula, there are 4 IFs and 4 close parentheses at the end.