How do I add 1 month to a specific date once that date has passed?

Question

I have an excel sheet that lists our current due date. The due dates are the same day of each month (9/2, 10/2, etc) and I want the due date to update once the old date passes so I don't have to do this manually or have a column for each month. I am sure there is a formula to do this but nothing is working.

The formula pictured is what I have right now and I'm not sure why it isn't working - although even this I think we'll have a problem with after the December due date passes. Any ideas?

Looking for a non-VBA solution.

Answer 1

You're in luck, no VBA required. In fact, there is an even simpler approach that doesn't require an IF test:

=DATE(YEAR(NOW()),MONTH(NOW())+(DAY(NOW())>2),2)

I hard-coded the 2nd day of the month based on the question, but it could be retrieved from a cell or extracted from the original due date.

Rather than test whether you've passed the target day of the month, this uses the fact that TRUE and FALSE are treated as 1 and 0, respectively. (DAY(NOW())>2) will be either true or false, so it will be treated like 1 if you've passed the 2nd day of the month, or 0 if you haven't. If you've passed that day, it adds 1 to the month.

Excel is smart enough to handle month "13" if this happens in December. It goes to January of the next year.