Fiscal Year Calculation

tcanning

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!

Paul Newcome

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

Paul Newcome

Try something like this...

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

tcanning

https://community.smartsheet.com/discussion/comment/322996#Comment_322996

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.

Paul Newcome

Are you able to provide a screenshot for reference?

tcanning

Let me know if this doesn't make sense!

Paul Newcome

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

Genevieve P.

Hi @tcanning

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

tcanning

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

https://community.smartsheet.com/discussion/comment/323326#Comment_323326

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!

Paul Newcome

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

tcanning

https://community.smartsheet.com/discussion/comment/324239#Comment_324239

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

Paul Newcome

Happy to help. 👍️