May 20 2022 12:29 PM
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?
May 20 2022 12:47 PM - edited May 20 2022 12:48 PM
Solution
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)))
May 20 2022 01:23 PM
May 20 2022 01:34 PM
May 20 2022 12:47 PM - edited May 20 2022 12:48 PM
Solution
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)))