Date Formula Error: Invalid Value
I'm trying to +/- months from a date, but I receive "invalid value" whenever there's a change in "year".
Example:
=DATE(YEAR(date12), MONTH(date12) + 6, DAY(date12))
If Date is 12/31/2020, then it creates an error because the new date needs to be in 2021 (6 months from 12/31/2020)
How can I fix this?
Best Answer
-
It looks like some things may have gotten shifted when you copied it over.
=DATE(YEAR([Key Date]10) + ROUNDDOWN((MONTH([Key Date]10) + [Months +/-]@row) / 12, 0) + IF(IF(MOD(MONTH([Key Date]10) + [Months +/-]@row, 12) = 0, 12, MOD(MONTH([Key Date]10) + [Months +/-]@row, 12)) = 12, -1), IF(MOD(MONTH([Key Date]10) + [Months +/-]@row, 12) = 0, 12, MOD(MONTH([Key Date]10) + [Months +/-]@row, 12)), DAY([Key Date]10))
Answers
-
Hi Mary,
Try this formula:
=IFERROR(DATE(YEAR(date@row), MONTH(date@row) + 6, DAY(date@row)), DATE(YEAR(date@row) + 1, MONTH(date@row) - 6, DAY(date@row)))
Hope this helps!
Have a nice day,
Mathieu | info@evolytion.com
evolytion.com
Mathieu | Workflow Consultant
info@evolytion.com
-
IS it a set number of months, or do the months vary?
-
The months vary. I put months in another column. How can I make the error formula more dynamic, so it'll always work no matter how many months +/-?
-
@Mary Lee Do a search in the community for "Edate". There are a handful of solutions that allow you to enter a number into a column and use that to adjust dates.
If you are unable to make one of those solutions work, feel free to let me know and we will try to figure something else out for you.
-
I tried this formula
=DATE(YEAR([Key date]10) + ROUNDDOWN((MONTH(YEAR([Key date]10) + [Months +/-]33) / 12, 0) + IF(IF(MOD(MONTH(YEAR([Key date]10) + [Months +/-]33, 12) = 0, 12, MOD(MONTH(YEAR([Key date]10) + [Months +/-]33)) = 12, -1), IF(MOD(MONTH(YEAR([Key date]10) + [Months +/-]33, 12) = 0, 12, MOD(MONTH(YEAR([Key date]10) + [Months +/-]33, 12)), DAY(YEAR([Key date]10))
I received Unparseable.
-
Can you post a link to where you got it from along with a screenshot of your sheet to include the column names?
-
Grabbed the formula from here: https://community.smartsheet.com/discussion/66572/what-is-formula-edate-from-excel-in-smartsheet
So I'm updating the End Date based on "Months" column plus a static Key Date in one cell.
-
It looks like some things may have gotten shifted when you copied it over.
=DATE(YEAR([Key Date]10) + ROUNDDOWN((MONTH([Key Date]10) + [Months +/-]@row) / 12, 0) + IF(IF(MOD(MONTH([Key Date]10) + [Months +/-]@row, 12) = 0, 12, MOD(MONTH([Key Date]10) + [Months +/-]@row, 12)) = 12, -1), IF(MOD(MONTH([Key Date]10) + [Months +/-]@row, 12) = 0, 12, MOD(MONTH([Key Date]10) + [Months +/-]@row, 12)), DAY([Key Date]10))
-
This works!!! Thank you so much!
-
Happy to help. 👍️
-
@Paul Newcome saves my day again 😅
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!