Calculation Quarters

BeCus

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

Kelly P.

@BeCus

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!

Kelly P.

@BeCus

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!

BeCus

Thank you! That did the trick.