Column Formula issue
HI all! I'm wondering why "#invalid data type" is showing after i convert this to a column formula, despite there being no date in the "Date-Reserve" column for it to pull from? I would expect it to be blank. I tried clearing the contents of those cells but it didn't seem to change the issue.
Best Answer
-
Give this a try:
=IF(ISDATE([Date-Reserve]@row), IF(AND(MONTH([Date-Reserve]@row) >= 7, MONTH([Date-Reserve]@row) <= 9), "Q1", IF(AND(MONTH([Date-Reserve]@row) >= 10, MONTH([Date-Reserve]@row) <= 12), "Q2", IF(AND(MONTH([Date-Reserve]@row) >= 1, MONTH([Date-Reserve]@row) <= 3), "Q3", "Q4"))), "")
Answers
-
It is difficult to say without seeing the formula.
-
=IF(AND(MONTH(Date@row) >= 7, MONTH([Date-Reserve]@row) <= 9), "Q1", IF(AND(MONTH([Date-Reserve]@row) >= 10, MONTH([Date-Reserve]@row) <= 12), "Q2", IF(AND(MONTH([Date-Reserve]@row) >= 1, MONTH([Date-Reserve]@row) <= 3), "Q3", "Q4")))
and
=IF(MONTH([Date-Reserve]@row) >= 7, YEAR([Date-Reserve]@row) + 1, YEAR([Date-Reserve]@row))
-
Are the references to Date@row and [Date-Reserve]@row correct?
-
@Carson Penticuff yes, they are referencing that Date-Reserve column with the dates
-
Is there also a column just called Date?
-
@Carson Penticuff yes there is! I see the error in the first formula, but now it's saying unparseable
=IF(AND(MONTH(Date-Reserve@row) >= 7, MONTH([Date-Reserve]@row) <= 9), "Q1", IF(AND(MONTH([Date-Reserve]@row) >= 10, MONTH([Date-Reserve]@row) <= 12), "Q2", IF(AND(MONTH([Date-Reserve]@row) >= 1, MONTH([Date-Reserve]@row) <= 3), "Q3", "Q4")))
However, i don't believe the 2nd formula had any error?
-
@Carson Penticuff updated to this, but says invalid
=IF(AND(MONTH([Date-Reserve]@row) >= 7, MONTH([Date-Reserve]@row) <= 9), "Q1", IF(AND(MONTH([Date-Reserve]@row) >= 10, MONTH([Date-Reserve]@row) <= 12), "Q2", IF(AND(MONTH([Date-Reserve]@row) >= 1, MONTH([Date-Reserve]@row) <= 3), "Q3", "Q4")))
-
You can try this for the second formula:
=IF(ISDATE([Date-Reserve]@row), IF(MONTH([Date-Reserve]@row) >= 7, YEAR([Date-Reserve]@row) + 1, YEAR([Date-Reserve]@row)), "")
That will rule out any possibility of any of the dates not being treated as dates by Smartsheet.
-
@Carson Penticuff thank you, that seemed to work! Any thoughts on the 1st formula?
-
Give this a try:
=IF(ISDATE([Date-Reserve]@row), IF(AND(MONTH([Date-Reserve]@row) >= 7, MONTH([Date-Reserve]@row) <= 9), "Q1", IF(AND(MONTH([Date-Reserve]@row) >= 10, MONTH([Date-Reserve]@row) <= 12), "Q2", IF(AND(MONTH([Date-Reserve]@row) >= 1, MONTH([Date-Reserve]@row) <= 3), "Q3", "Q4"))), "")
-
@Carson Penticuff amazing, thank you!!
-
Happy to help. I find it good practice to throw in an ISDATE() for validation anytime I'm working with dates. There are just too many ways dates can go wrong.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.3K Get Help
- 392 Global Discussions
- 213 Industry Talk
- 447 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives