SOLVED

# Auto populate same date, next coming year

Copper Contributor

# Auto populate same date, next coming year

Hello Excel Community!

I keep finding something SIMILAR to what I need, but not exactly.

I have a list of contract dates in oldest to newest order.  I need excel to automatically find the same date for the next coming year that is NOT in the past.

For example:  I have a contract end date on 11/11/2020.  I know that I can simply add 12 months to it with the EDATE formula, but that would be 11/11/2021.  I need Excel to find the next 11/11 that is not already passed.  Is this possible?

3 Replies
best response confirmed by allieZ (Copper Contributor)
Solution

# Re: Auto populate same date, next coming year

Here's a quick and dirty formula. If you need something more sophisticated, come back with the details. A3 in this example is the prior contract end date.

=IF(A3+365.25>TODAY(),A3+365.25,DATE(YEAR(TODAY()),MONTH(A3),DAY(A3)))

# Re: Auto populate same date, next coming year

I think this works perfectly! Took a second to really understand how this formula is working, but I think i got it.
Thank you!

# Re: Auto populate same date, next coming year

Basically it says
IF the date created when you add 365.25** to A3 is greater than today, then use that date because it is in the future,
ELSE, create a date using today's year and the month and day from A3.

**To account for leap years, that was a way I used to do it when writing programs in Basic, 50 years ago.
1 best response

Accepted Solutions
best response confirmed by allieZ (Copper Contributor)
Solution

# Re: Auto populate same date, next coming year

Here's a quick and dirty formula. If you need something more sophisticated, come back with the details. A3 in this example is the prior contract end date.

=IF(A3+365.25>TODAY(),A3+365.25,DATE(YEAR(TODAY()),MONTH(A3),DAY(A3)))