Column Formula issue

Btrombler

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.

Carson Penticuff

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

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

It is difficult to say without seeing the formula.

Btrombler

@Carson Penticuff

=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))

Carson Penticuff

Are the references to Date@row and [Date-Reserve]@row correct?

Btrombler

@Carson Penticuff yes, they are referencing that Date-Reserve column with the dates

Carson Penticuff

Is there also a column just called Date?

Btrombler

@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?

Btrombler

@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")))

Carson Penticuff

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.

Btrombler

@Carson Penticuff thank you, that seemed to work! Any thoughts on the 1st formula?

Carson Penticuff

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"))), "")

Btrombler

@Carson Penticuff amazing, thank you!!

Carson Penticuff

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.