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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This works!!! Thank you so much!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome saves my day again 😅
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!