Creating dates to increment by month, how do I move to a new year when month equals 12?
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
Best Answer
-
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)))
Answers
-
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)))
-
Thank you for your prompt response. The formula works perfectly for my application!
-
Happy to help! 👍️
-
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).
-
@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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!