How Can I Connect a string of IF/AND and ISBLANK statements Into a Working Formula?

David Brandt

There is probably a much sexier way to solve this but separately these 3 groups of statements actually work just fine. I just haven't been able to string them together and get Smartsheet to spit out the info for a given input. Would love to employ a shorter, more elegant solution but would be just as happy if someone can help me understand why -- when strung together -- the only output I get is FY21, Q1. (The sheet doesn't give me an error/unparseable/etc.) What's wrong? How best? Much appreciated!

=IF(AND(Start@row >= DATE(2020, 2, 1), End@row < DATE(2020, 5, 1)), "FY21, Q1",

IF(AND(Start@row >= DATE(2020, 5, 1), End@row < DATE(2020, 8, 1)), "FY21, Q2",

IF(AND(Start@row >= DATE(2020, 8, 1), End@row < DATE(2020, 11, 1)), "FY21, Q3",

IF(AND(Start@row >= DATE(2020, 11, 1), End@row < DATE(2021, 2, 1)), "FY21, Q4",

IF(AND(Start@row >= DATE(2021, 2, 1), End@row < DATE(2021, 5, 1)), "FY22, Q1",

IF(AND(Start@row >= DATE(2021, 5, 1), End@row < DATE(2021, 8, 1)), "FY22, Q2",

IF(AND(Start@row >= DATE(2021, 8, 1), End@row < DATE(2021, 11, 1)), "FY22, Q3",

IF(AND(Start@row >= DATE(2021, 11, 1), End@row < DATE(2022, 2, 1)), "FY22, Q4",

IF(AND(Start@row >= DATE(2022, 2, 1), End@row < DATE(2022, 5, 1)), "FY23, Q1",

IF(AND(Start@row >= DATE(2022, 5, 1), End@row < DATE(2022, 8, 1)), "FY23, Q2",

IF(AND(Start@row >= DATE(2022, 8, 1), End@row < DATE(2022, 11, 1)), "FY23, Q3",

IF(AND(Start@row >= DATE(2022, 11, 1), End@row < DATE(2023, 2, 1)), "FY23, Q4",

IF(AND(Start@row >= DATE(2021, 2, 1), (ISBLANK(End@row))), "FY22, Q1 TBD", 

IF(AND(Start@row >= DATE(2021, 5, 1), (ISBLANK(End@row))), "FY22, Q2 TBD", 

IF(AND(Start@row >= DATE(2021, 8, 1), (ISBLANK(End@row))), "FY22, Q3 TBD", 

IF(AND(Start@row >= DATE(2021, 11, 1), (ISBLANK(End@row))), "FY22, Q4 TBD", 

IF(AND(Start@row >= DATE(2022, 2, 1), (ISBLANK(End@row))), "FY23, Q1 TBD", 

IF(AND(Start@row >= DATE(2022, 5, 1), (ISBLANK(End@row))), "FY23, Q2 TBD", 

IF(AND(Start@row >= DATE(2022, 8, 1), (ISBLANK(End@row))), "FY23, Q3 TBD", 

IF(AND(Start@row >= DATE(2022, 11, 1), (ISBLANK(End@row))), "FY23, Q4 TBD",

IF(AND((ISBLANK(Start@row)), (ISBLANK(End@row))), "Please estimate a start date")))))))))))))))))))))

Paul Newcome

Lets try something like this...

To generate the fiscal year:

="FY" + RIGHT(YEAR(Start@row) + 1, 2)

Next we generate the quarter:

="FY" + RIGHT(YEAR(Start@row) + 1, 2) + ", Q" + IF(OR(MONTH(Start@row) = 1, MONTH(Start@row) >= 11), 4, IF(MONTH(Start@row) < 5, 1, IF(MONTH(Start@row) < 8, 2, 3)))

Next we tack on the bit for "TBD" if the End is blank:

="FY" + RIGHT(YEAR(Start@row) + 1, 2) + ", Q" + IF(OR(MONTH(Start@row) = 1, MONTH(Start@row) >= 11), 4, IF(MONTH(Start@row) < 5, 1, IF(MONTH(Start@row) < 8, 2, 3))) + IF(End@row = "", "TBD")

Then finally we go back to the beginning and add the bit about if both Start and End are blank:

=IF(AND(Start@row = "", End@row = ""), "Please estimate a start date", "FY" + RIGHT(YEAR(Start@row) + 1, 2) + ", Q" + IF(OR(MONTH(Start@row) = 1, MONTH(Start@row) >= 11), 4, IF(MONTH(Start@row) < 5, 1, IF(MONTH(Start@row) < 8, 2, 3))) + IF(End@row = "", "TBD"))

Kelly Drake

I think part of the issue you're running into is too many parentheses. You do not need to wrap ISBLANK inside parentheses. It was also giving me a weird error with the two cells being blank at the end.

BTW - this might work as a "more elegant" solution.... it might be a little longer but you won't have to continually edit the formula to add the next quarter. (Though I do want to call out that your above formula seems to indicate that the projects only ever occur within the cofines of a particular fiscal quarter so if that's not hte case you may need to modify a little). This is written to essentially note the quarter it starts and appends TBD if there is no end date.

=IF(AND(ISBLANK(Start@row), ISBLANK(End@row)), "Please estimate a start date", IF(AND(NOT(ISBLANK(End@row)), OR(MONTH(Start@row) = 2, MONTH(Start@row) = 3, MONTH(Start@row) = 4)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q1", IF(AND(NOT(ISBLANK(End@row)), OR(MONTH(Start@row) = 5, MONTH(Start@row) = 6, MONTH(Start@row) = 7)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q2", IF(AND(NOT(ISBLANK(End@row)), OR(MONTH(Start@row) = 8, MONTH(Start@row) = 9, MONTH(Start@row) = 10)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q3", IF(AND(NOT(ISBLANK(End@row)), OR(MONTH(Start@row) = 11, MONTH(Start@row) = 12, MONTH(Start@row) = 1)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q4", IF(AND(ISBLANK(End@row), OR(MONTH(Start@row) = 2, MONTH(Start@row) = 3, MONTH(Start@row) = 4)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q1 TBD", IF(AND(ISBLANK(End@row), OR(MONTH(Start@row) = 5, MONTH(Start@row) = 6, MONTH(Start@row) = 7)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q2 TBD", IF(AND(ISBLANK(End@row), OR(MONTH(Start@row) = 8, MONTH(Start@row) = 9, MONTH(Start@row) = 10)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q3 TBD", IF(AND(ISBLANK(End@row), OR(MONTH(Start@row) = 2, MONTH(Start@row) = 3, MONTH(Start@row) = 4)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + 1) + " Q4 TBD"))))))))

Paul Newcome

Lets try something like this...

To generate the fiscal year:

="FY" + RIGHT(YEAR(Start@row) + 1, 2)

Next we generate the quarter:

="FY" + RIGHT(YEAR(Start@row) + 1, 2) + ", Q" + IF(OR(MONTH(Start@row) = 1, MONTH(Start@row) >= 11), 4, IF(MONTH(Start@row) < 5, 1, IF(MONTH(Start@row) < 8, 2, 3)))

Next we tack on the bit for "TBD" if the End is blank:

="FY" + RIGHT(YEAR(Start@row) + 1, 2) + ", Q" + IF(OR(MONTH(Start@row) = 1, MONTH(Start@row) >= 11), 4, IF(MONTH(Start@row) < 5, 1, IF(MONTH(Start@row) < 8, 2, 3))) + IF(End@row = "", "TBD")

Then finally we go back to the beginning and add the bit about if both Start and End are blank:

=IF(AND(Start@row = "", End@row = ""), "Please estimate a start date", "FY" + RIGHT(YEAR(Start@row) + 1, 2) + ", Q" + IF(OR(MONTH(Start@row) = 1, MONTH(Start@row) >= 11), 4, IF(MONTH(Start@row) < 5, 1, IF(MONTH(Start@row) < 8, 2, 3))) + IF(End@row = "", "TBD"))

Kelly Drake

I think part of the issue you're running into is too many parentheses. You do not need to wrap ISBLANK inside parentheses. It was also giving me a weird error with the two cells being blank at the end.

BTW - this might work as a "more elegant" solution.... it might be a little longer but you won't have to continually edit the formula to add the next quarter. (Though I do want to call out that your above formula seems to indicate that the projects only ever occur within the cofines of a particular fiscal quarter so if that's not hte case you may need to modify a little). This is written to essentially note the quarter it starts and appends TBD if there is no end date.

=IF(AND(ISBLANK(Start@row), ISBLANK(End@row)), "Please estimate a start date", IF(AND(NOT(ISBLANK(End@row)), OR(MONTH(Start@row) = 2, MONTH(Start@row) = 3, MONTH(Start@row) = 4)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q1", IF(AND(NOT(ISBLANK(End@row)), OR(MONTH(Start@row) = 5, MONTH(Start@row) = 6, MONTH(Start@row) = 7)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q2", IF(AND(NOT(ISBLANK(End@row)), OR(MONTH(Start@row) = 8, MONTH(Start@row) = 9, MONTH(Start@row) = 10)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q3", IF(AND(NOT(ISBLANK(End@row)), OR(MONTH(Start@row) = 11, MONTH(Start@row) = 12, MONTH(Start@row) = 1)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q4", IF(AND(ISBLANK(End@row), OR(MONTH(Start@row) = 2, MONTH(Start@row) = 3, MONTH(Start@row) = 4)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q1 TBD", IF(AND(ISBLANK(End@row), OR(MONTH(Start@row) = 5, MONTH(Start@row) = 6, MONTH(Start@row) = 7)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q2 TBD", IF(AND(ISBLANK(End@row), OR(MONTH(Start@row) = 8, MONTH(Start@row) = 9, MONTH(Start@row) = 10)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + " Q3 TBD", IF(AND(ISBLANK(End@row), OR(MONTH(Start@row) = 2, MONTH(Start@row) = 3, MONTH(Start@row) = 4)), "FY" + RIGHT(YEAR(Start@row) + 1, 2) + 1) + " Q4 TBD"))))))))

David Brandt

@Kelly Drake and @Paul Newcome... Holy Moly! Awe inspired. Thank you Both!! And for the additional edu and thoughts (Kelly) on it. I'll have to spend time with those formulas to fully understand the concepts and what each is doing so I can build on them, but these work wonderfully. That Paul reduced my brute force tactic to 3 lines is embarrassing and encouraging all at the same time.

Kelly Drake

Yeah not gonna lie - I was hella impressed by @Paul Newcome 's work too!

I often think of the end results as each option but Paul broke it down into figuring out the segments of the options.

Paul Newcome

@David Brandt and @Kelly Drake The secret to that was looking at everything that we were repeating over and over and over again. When I see something repeated multiple times through a formula, the first thing I do is look to see if we can write the static portion(s) once and then automate the variable portion(s).

Every single output started with "FY" and was then followed by the year +1. Every output then followed with "comma/space/Q". The variable data after that (since we already took care of the year) is based on the month. That took a nested IF statement. For that I got the "tricky" part out of the way first with the OR statement, then used the built in logic of nested IFs where they stop on the first true value. If it makes it to the next IF, then every IF before that must be false. Once we took care of that we could automate the "TBD" portion by looking at the fact that all outputs containing that had a filled in Start and blank End. Since the quarters were based on the start, the only thing left to look at was the End date and whether or not that was blank. Then we took the originally last portion of both being blank and moved it to the front.

So if both are blank, then output the "please estimate......" string. If that is false meaning at least one is filled in, then we move to the "FY....." string based on the start date and end with the End date.

The only possible outcomes not accounted for would be if the start and end dates were in different quarters or if for whatever reason there was no start date but there was an end date.

And last but certainly not least... Don't be embarrassed. I spent about 2 years working 40+ hours per week tackling some pretty crazy challenges through formulas building out a wicked complex workspace for an employer and have since been rather active here in the community. There is A LOT of experience, lessons learned the hard way, coffee, and frustration behind my solutions. Hahaha. It'll come with time. No worries.