# Fiscal Year Calculation

Options
✭✭
edited 03/15/22

Hello,

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Try something like this...

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

• ✭✭
Options

Thanks so much for taking a look at this and working towards the solution!

This is working except the parent task is inaccurately showing "No Completion Date", sometimes there are no sub-tasks so we would want the date to still show up if possible. The "No Completion Date" helps us when there is old data where a completion date was not added.

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot for reference?

• ✭✭
Options

Let me know if this doesn't make sense!

• ✭✭✭✭✭✭
Options

Try this:

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

Options

I see you marked Paul's answer as not resolving your question. Are you getting an error message or an incorrect result?

• ✭✭
Options

My apologies for the delay I am trying to figure out the best way to word what still isn't working.

I am hoping to have each task read the date of the parent task and base the calculation on that.

I have put in the formula suggested and there are a few instances where it is still not calculating correctly.

1. This is the parent task and it should have the completed on 2/14/2020 and the date calculated as FY 2020
2. The parent task for this has been completed 2/14/2020 and should be calculated as FY 2020
3. This is the parent task and it should have the completed on 2/20/2020 and the date calculated as FY 2020
4. This is the parent task and it should have the completed on 4/02/2020 and the date calculated as FY 2020

I think we are almost there. I truly appreciate all the time and effort you are putting in to help solve this!

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

Yes that did the trick, thank you so much for your time with this!!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!