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

Josh Stoner
Josh Stoner ✭✭
edited 06/16/22 in Formulas and Functions

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?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!