HELP! Column Formula working on some rows - not others
Hi!
I'm stumped on this one. I am using a column formula to apply a date 21 days prior to the date in another column. Everything (formatting, etc) is the same on all the rows, but I keep getting "Invalid Value" errors on some rows and in other rows it works great.
Does anyone have any ideas??
Formula: =DATE(YEAR([GT TARGET: ATL BTL C&E Data Deadline]@row), MONTH([GT TARGET: ATL BTL C&E Data Deadline]@row), DAY([GT TARGET: ATL BTL C&E Data Deadline]@row) - 21)
Screenshot:
Best Answer
-
The issue here is you are subtracting 21 from the day value. How ever you have this in the DATE function. Creating an issue where if the date you are pulling from is not above 21 it will not return a valid date. Instead move the -21 outside the DATE function.
=DATE(YEAR([GT TARGET: ATL BTL C&E Data Deadline]@row), MONTH([GT TARGET: ATL BTL C&E Data Deadline]@row), DAY([GT TARGET: ATL BTL C&E Data Deadline]@row))-21
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
Hello @HSabin
It is most likely happening because there are not 21 days left in those months.
You can't have any days that are equal to 0 or negative.
-
The issue here is you are subtracting 21 from the day value. How ever you have this in the DATE function. Creating an issue where if the date you are pulling from is not above 21 it will not return a valid date. Instead move the -21 outside the DATE function.
=DATE(YEAR([GT TARGET: ATL BTL C&E Data Deadline]@row), MONTH([GT TARGET: ATL BTL C&E Data Deadline]@row), DAY([GT TARGET: ATL BTL C&E Data Deadline]@row))-21
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Hi @MichaelTCA, I see what you're saying but I thought the formula accounted for month, day and year. Do you have any suggestions on correcting the formula to back out into the previous month?
-
@Mark.poole - thank you! That worked. And just before I saw your reply, I simplified the whole thing by simply typing "=" then clicking on the cell in the column, "-21" and it returned perfectly as well.
THank you!! -
Absolutely. I always try to assume there is a reasoning behind using one function or another. Unless its overly complicated and not working. I am glad you found a solution that works best for you.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!