# Calculation Quarters

Options
✭✭

Using this formula I get FY2023 Q4 (example) but if the End Date field is empty I get unparseable. How can I fix this?

="FY" + YEAR([End Date]@row) + " Q" + IF(MONTH([End Date]@row) = 1, "4", IF(MONTH([End Date]@row) = 2, "4", IF(MONTH([End Date]@row) = 3, "4", IF(MONTH([End Date]@row) = 4, "1", IF(MONTH([End Date]@row) = 5, "1 ", IF(MONTH([End Date]@row) = 6, "1 ", IF(MONTH([End Date]@row) = 7, "2", IF(MONTH([End Date]@row) = 8, "2", IF(MONTH([End Date]@row) = 9, "2", IF(MONTH([End Date]@row) >= 10, "3 ", " "))))))))))

• ✭✭✭✭✭✭
Options

Either of these formulas will work. The first checks to see if the End Date is a date before continuing with the formula (if there is no end date, the results are empty) and the second just places a blank value if the formula can't work due to a lack of an end date.

=IF(ISDATE([End Date]@row), "FY" + YEAR([End Date]@row) + " Q" + IF(MONTH([End Date]@row) = 1, "4", IF(MONTH([End Date]@row) = 2, "4", IF(MONTH([End Date]@row) = 3, "4", IF(MONTH([End Date]@row) = 4, "1", IF(MONTH([End Date]@row) = 5, "1 ", IF(MONTH([End Date]@row) = 6, "1 ", IF(MONTH([End Date]@row) = 7, "2", IF(MONTH([End Date]@row) = 8, "2", IF(MONTH([End Date]@row) = 9, "2", IF(MONTH([End Date]@row) >= 10, "3 ", " ")))))))))))

=IFERROR("FY" + YEAR([End Date]@row) + " Q" + IF(MONTH([End Date]@row) = 1, "4", IF(MONTH([End Date]@row) = 2, "4", IF(MONTH([End Date]@row) = 3, "4", IF(MONTH([End Date]@row) = 4, "1", IF(MONTH([End Date]@row) = 5, "1 ", IF(MONTH([End Date]@row) = 6, "1 ", IF(MONTH([End Date]@row) = 7, "2", IF(MONTH([End Date]@row) = 8, "2", IF(MONTH([End Date]@row) = 9, "2", IF(MONTH([End Date]@row) >= 10, "3 ", " ")))))))))), "")

Hope this helps!

• ✭✭✭✭✭✭
Options

Either of these formulas will work. The first checks to see if the End Date is a date before continuing with the formula (if there is no end date, the results are empty) and the second just places a blank value if the formula can't work due to a lack of an end date.

=IF(ISDATE([End Date]@row), "FY" + YEAR([End Date]@row) + " Q" + IF(MONTH([End Date]@row) = 1, "4", IF(MONTH([End Date]@row) = 2, "4", IF(MONTH([End Date]@row) = 3, "4", IF(MONTH([End Date]@row) = 4, "1", IF(MONTH([End Date]@row) = 5, "1 ", IF(MONTH([End Date]@row) = 6, "1 ", IF(MONTH([End Date]@row) = 7, "2", IF(MONTH([End Date]@row) = 8, "2", IF(MONTH([End Date]@row) = 9, "2", IF(MONTH([End Date]@row) >= 10, "3 ", " ")))))))))))

=IFERROR("FY" + YEAR([End Date]@row) + " Q" + IF(MONTH([End Date]@row) = 1, "4", IF(MONTH([End Date]@row) = 2, "4", IF(MONTH([End Date]@row) = 3, "4", IF(MONTH([End Date]@row) = 4, "1", IF(MONTH([End Date]@row) = 5, "1 ", IF(MONTH([End Date]@row) = 6, "1 ", IF(MONTH([End Date]@row) = 7, "2", IF(MONTH([End Date]@row) = 8, "2", IF(MONTH([End Date]@row) = 9, "2", IF(MONTH([End Date]@row) >= 10, "3 ", " ")))))))))), "")

Hope this helps!

• ✭✭
Options

Thank you! That did the trick.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!