Fiscal Year Calculation

tcanning ✭✭
edited 03/15/22 in Formulas and Functions


I am trying to have a column that calculates a fiscal year automatically based on whether the parent Completed Date falls before or after July 1.

Something that follows this logic: If the parent "Completed Date" month is less than 7 (July) display "FY 'Parent Year'", otherwise display "FY 'Parent Year + 1'", if there is no parent "Completed Date" show "No Completion Date"

I have the equation below working, but I am not sure where to add ANCESTORS to each argument. Whenever I have added it I either receive an error that the syntax isn't quite right or that there is a "No Completion Date" in every row.

=IFERROR(IF(MONTH([Completed Date]@row) < 7, "FY " + (YEAR([Completed Date]@row)), "FY " + (YEAR([Completed Date]@row) + 1)), "No Completion Date")

Would love to hear any ideas that you all might have for this.

Thank you!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I see where the issue is. I was counting the number of children and assuming that if the count was zero then it was a child row that had a parent row. This isn't always true. Lets try this instead. If it is on the first level, pull the completed date, otherwise pull the parent.

    =IFERROR("FY" + (YEAR(IF(COUNT(ANCESTORS([Completed Date]@row)) = 0, [Completed Date]@row, PARENT([Completed Date]@row))) + IF(MONTH(IF(COUNT(ANCESTORS([Completed Date]@row)) = 0, [Completed Date]@row, PARENT([Completed Date]@row)))>= 7, 1)), "No Completion Date")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!