Quarter formula with some exceptions

chris carrero

Hi Everyone,

What I am trying to do is create a formula that looks at two things, the status column to check if the status is not started or draft. If it is either it needs to return a blank. If it is anything else, it needs to check the End Date column and determine what quarter it is and return the appropriate value Q1, Q2, Q3, Q4

=IFERROR(IF(OR(STATUS@row = "Not Started", STATUS@row = "Draft"), "", IF(OR(MONTH([End Date]@row) = 1, MONTH([End Date]@row) = 2, MONTH([End Date]@row) = 3), "Q1")), "")

it works for Q1,

=IFERROR(IF(OR(STATUS@row = "Not Started", STATUS@row = "Draft"), "", IF(OR(MONTH([End Date]@row) = 1, MONTH([End Date]@row) = 2, MONTH([End Date]@row) = 3), "Q1"), IF(OR(MONTH([End Date]@row) = 4, MONTH([End Date]@row) = 5, MONTH([End Date]@row) = 6), "Q2")), "")

When I start adding other Quarters, it returns #incorrect argument set. I'm super new to Smartsheets so I'd appreciate it if someone could show me where I went wrong.

Thanks!

-Chris

Mike Wilday

There was one closing paragraph that was out of place.

You shouldn't close any IF statements till the very end. In your example, you have a closing paragraph after "Q1" which ends the IF statement. Try this one:

=IFERROR(IF(OR(STATUS@row = "Not Started", STATUS@row = "Draft"), "", IF(OR(MONTH([End Date]@row) = 1, MONTH([End Date]@row) = 2, MONTH([End Date]@row) = 3), "Q1", IF(OR(MONTH([End Date]@row) = 4, MONTH([End Date]@row) = 5, MONTH([End Date]@row) = 6), "Q2"))), "")

Let me know how it goes.

Mike Wilday

There was one closing paragraph that was out of place.

You shouldn't close any IF statements till the very end. In your example, you have a closing paragraph after "Q1" which ends the IF statement. Try this one:

=IFERROR(IF(OR(STATUS@row = "Not Started", STATUS@row = "Draft"), "", IF(OR(MONTH([End Date]@row) = 1, MONTH([End Date]@row) = 2, MONTH([End Date]@row) = 3), "Q1", IF(OR(MONTH([End Date]@row) = 4, MONTH([End Date]@row) = 5, MONTH([End Date]@row) = 6), "Q2"))), "")

Let me know how it goes.

chris carrero

Thanks Mike, that was it!! i'm still struggling with simple syntax.

Mike Wilday

With nested IF statements you need to close all of the statements at the end.

=IF(condition, result, IF(condition, result, IF(condition, result, IF(condition, result, final else))))

Four open IF's and 4 closed at the end.