I want to add a column that lists dates 6 months from other date column
Have tried a few different formulas and not working
Best Answer
-
You can try putting this in. It is just the same formula with the IFERROR() removed. If that results in errors in those same cells, the date in that specific row may not be formatted correctly.
=DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row))
Answers
-
=IFERROR(DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row)),"")
This one should work. You will need to change [First Date] to whatever your date column name. You will also need to ensure that both the [First Date] column and the column you want the +6 month date to appear in are both formatted as dates. The IFERROR() is in place in case there are any blank cells somewhere in the column.
-
Thanks Carson, that worked!
But its still not dropping in for some cells in the column and im not sure why?
-
You can try putting this in. It is just the same formula with the IFERROR() removed. If that results in errors in those same cells, the date in that specific row may not be formatted correctly.
=DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row))
-
Worked! Thanks Carson!
-
Happy to help, I'm glad it worked!
-
Hi again,
another issue - if the +6 months runs into the next year, its coming up as an error. Any idea how to solve this?
-
I failed to take into account wrapping into the next year. The first formula is for one month into the future, the second is for six months.
=IF(MONTH([First Date]@row) = 12, DATE(YEAR([First Date]@row) + 1, 1, DAY([First Date]@row)), DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row)))
=IF(MONTH([First Date]@row) > 6, DATE(YEAR([First Date]@row) + 1, MONTH([First Date]@row) - 6, DAY([First Date]@row)), DATE(YEAR([First Date]@row), MONTH([First Date]@row) + 1, DAY([First Date]@row)))
-
Worked - Thank you!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!