Creating dates to increment by month, how do I move to a new year when month equals 12?

Susan Hildebrant

Here is the formula I used: =DATE(YEAR(Date3), MONTH(Date3) + 1). It works great for dates up to the 12th month (December). How

do I modify the formula to go to January of the next year, e.g. 12/01/20 to 01/01/21?

Thanks, Susan

Paul Newcome

There are a few different ways to do this. My preference is an IFERROR statement.

=IFERROR(DATE(YEAR(Date@row), MONTH(Date@row) + 1, DAY(Date@row)), DATE(YEAR(Date@row) + 1, 1, DAY(Date@row)))

Paul Newcome

There are a few different ways to do this. My preference is an IFERROR statement.

=IFERROR(DATE(YEAR(Date@row), MONTH(Date@row) + 1, DAY(Date@row)), DATE(YEAR(Date@row) + 1, 1, DAY(Date@row)))

Susan Hildebrant

Thank you for your prompt response. The formula works perfectly for my application!

Paul Newcome

Happy to help! 👍️

Robin Herrick

I've used a slightly different formula to go backwards in time generating text dates rather than actual dates.

=IF((MONTH(TODAY()) - 9) > 0, (MONTH(TODAY()) - 9) + "/" + YEAR(TODAY()), (MONTH(TODAY()) + 3) + "/" + (YEAR(TODAY()) - 1))

Increment the 9 up and the 3 down to suit the time jump (always adding to 12).

Paul Newcome

@Robin Herrick Yes. That will work to generate text strings that look like dates, but they cannot be used as actual dates in filters. reports, or further calculations. You would need to use the IFERROR(DATE(.....), DATE(.....)) method to be able to output actual date values.

There is an EDATE solution that I developed floating around here in the community somewhere if you wanted to be able to put a number (positive or negative) in a cell and then add/subtract that number of months from a date.

Robin Herrick

They can be used in the application I have which is to match string dates to string dates to filter results. I have appended the string date to another string to enable one-shot vlookups. It's sooooo cooool and elegant.