fiscal year formula, #invalid operation in blank cell when converting to a column formula

Josh Stoner

I created a column called fiscal yr counter to be used in other formulas. The fiscal yr runs from July 1 to June 30. the formula works off of an award date column. Current formula for counter is:

My issue is when I convert it to a column formula, it proceeds to give me #invalidoperation in some of the column cells where there isn't a date in the award date column. If no award date was picked previously, the fy counter cell at the row stays blank. If there was an award date in there and I deleted it out(for testing purposes), then it will produce the message. See below:

Is there a way I can correct it so that the fy counter will stay blank in the cell and only show when there is an award date instead of picking up if something was previously in the award date cell?

Paul Newcome

You would need to wrap the formula in an IFERROR statement.

=IFERROR("FY" + ..................)), "")

Paul Newcome

You would need to wrap the formula in an IFERROR statement.

=IFERROR("FY" + ..................)), "")

Josh Stoner

@Paul Newcome

So the formula would now look like:

=IFERROR("FY"+IF(MONTH([Award Date]@row)>6,YEAR([Award Date]@row)+1,YEAR([Award Date]@row),"")

Correct?

Josh Stoner

@Paul Newcome

It worked! Thanks Paul!

Paul Newcome

Happy to help. 👍️